Both Microsoft and Google (and probably others) have a flawed method of calculating standard deviations. They hit the limits of the calculation engine with large numbers. They have no interest in fixing the flaw - perhaps so you always get the same perhaps wrong answer with different versions of Excel.

Try SD of 1,2,3 = 1

But add 1E+16 to each number and you get zero. Add a number between 1E+15 and 1E+16 and you get a range of numbers for the SD.

Taking the mean of the series from each number before calculating the SD seems to solve the problem

May be a vaguely useful function for the toolbox.

David,

on Excel 2010 and Excel 2016 I get the expected results:

Excel 2016:

Excel 2010:

Which Excel version produces incorrect results?

Hi Daniel

1E+16 - not 1E+6

You would rarely get this situation in practice, but anyone using Excel

seriously should be aware of the limitations

Below (Excel 2016, but version matters little):

Col A: 1,2,3 and the expected SD 1

Col B: Result when 1E+16 is added to each number

Its really about the *machine epsilon*

I was forced to learn a bit about Excel when I was doing some numerical

modelling and ended up with some 400 spreadsheets. I produced (spaghettli

code) to loop through the work sheets and produce an Index worksheet. I

used Ctl Shift G in a blank square to return to the Index sheet and then

the same keystrokes in that sheet to go to a particular sheet, Cell A1 in

each sheet was reserved for a comment which was listed in the index, along

with the Y axis for graphs.

Another macro I still find useful saved Charts as PNG files in the same

directory as the spreadsheet, using the name of the chart tag as the

filename. When I was running my numerical models, the model parameters were

inserted in the chart tag and made a unique file name. Simple code, but

saved me a lot of time.

Another function I used sometimes was for calculating the geometric mean of

a series when some of the numbers were zero or negatived. This occurs when

the numbers produced by an instrument sometimes go negative. I’d replace

the zeros/ small negative numbers during the GM calculation with a small

number/ detection limit.

Feel free to use the ideas

Cheers

David

Oh, I see.

As for a UDF (user-defined function), I’d be a bit hesitant to add (another) one. UDFs tend not to work very well when a workbook is opened on a different computer. I used to have a `=panova`

function in the older versions that computed the p value for an analysis of variance.

There’s the screenshot command right now to quickly export charts to PNG files. It’d be fairly easy to construct a default file name based on the chart name.

Thanks for your suggestions!