Chart skills or not

Today being a baking day, I thought I would take a peek at the trend line, a peek which I thought would be a trivial matter with the power of Excel 365 at my elbow.

Click on the date column of the breadsheet, click on insert line graph and I get a chart which is rubbish. Do it again, same result. Maybe I need to include a column for the serial number, and select two columns rather than just the one. Still rubbish. What on earth is going on? This is a chart which I have produced several times before, and I must have produced hundreds of line charts over the years.

Has there been some cunning change in the charts part of the Excel world?

Should I try help? I do try help, but it is not very helpful. Google does turn up anything of use either.

This is now more than half an hour since I started and I am starting to get a bit irritated.

When, all of a sudden, for no particular reason, I notice that there is an error in the date at around row 350. I have tried to make the day in the month 45, with the result that Excel does not recognise this date as a date, knocking on all the way to the rubbish I started out with, without any kind of warning that maybe sir overlooked a little problem at row 350. Maybe Cortana is not as bright as she is cracked up to be.

Correct the date, having to guess between 4 and 5 at this remove in time, and all is well.

I now try to recover my poise by being clever. Is the line I get a straight line? Is the rate of baking steady over time, or does it speed up and slow down with the phases of the moon, the ambient temperature or whatever?

At which point I discover that I have no idea how to do this on a chart, short of taking half a day to write some visual basic code which does something complicated with date fields and computes intervals in days between dates, something that the visual basic DateDiff function is actually quite good at. And as I type this, I start to come clear on how to write the code, at least on one way to write it, and to get it down to maybe an hour or so. But it is not going to happen. I shall stick with being irritated.

Lies etc

The snip being taken from a graph from my bread workbook which purports to show in red the mean number of days per batch, on the right hand scale, with the mean computed over the previous 50 batches. The blue shows total days since the beginning of bread, on the left hand scale, for these purposes running from day 188, otherwise mid July 2011, to day 2371, a period of nearly six years. Batches along the bottom, starting at batch 51. I dare a bit more effort would get the labels right.

But more important, why do we have a blue line which looks very straight but a two humped red line? Two humps over six years? Further investigation needed. Have I blundered?

Which all goes to show that whoever invented the well known phrase about lies and statistics clearly knew his onions.

It also served to remind me of the power of the Excel 'datediff' function, a function which will compute the interval between two dates, supplied in ordinary calendar format, and give you the answer in days, weeks or whatever you fancy. I dare say I could write such a function for myself, but I hate to think how long it would take to be sure that I had got it right. So I am glad that MS took on this particular bit of work for me.

PS: it will also do hours, minutes and seconds if you include times with your dates, but it does not do moons, non trivial as the number of days in a moon, as seen from the earth, varies from about 29.18 to about 29.93. A tricky sort of number, no doubt accounting for all kinds of lunar mumbo jumbo. Perhaps native peoples everywhere should put in a bid for an enhancement.