Problems with differences in averages 1

[Stoffel24]

When I calculate the following 2 formulas, I get a slightly different result. Can anyone explain this?
=AVERAGE(Sheet1!$B$11:$B$14)
=AVERAGE(Sheet1!$B$11,$B$12,$B$13,$B$14)

 

[Zathras]

What are the values in Sheet1 B11 thru B14?
What are the values in the active sheet B12 thru B14?

 

[Stoffel24]

Ok, the values are as follows:
9.71
9.73
9.89
9.82

 

[Zathras]

You didn't exactly answer my questions.

The average of the four values is 9.7875

I'm guessing that your formulas are on Sheet2.
I'm guessing that your second formula gives 9.71.
That is the result when B12, B13 and B14 are blank on sheet 2.
If you put the values from Sheet1 B12 thru B14 into Sheet2 B12 thru B14 you should get the same answer.

In other words, by using
=AVERAGE(Sheet1!$B$11,$B$12,$B$13,$B$14)
only B11 is taken from Sheet1, the others are taken from the active sheet, which I am guessing is Sheet2.

Does that clear it up?

 

[bryanbayfield]

Might be a bug in Excel's average function. I know their STDEV function is not very reliable, maybe there's also something going on here.

Try coding your own average function in VBA (I think you'd have to call it something other than AVERAGE, not sure) and see whether you get the same problem. Use double floating precision in your variables.

What are the differences of the order of? ie are they differing by tenths, hundredths etc etc?

 

[xlbo]

erm - 'cos you are averaging Sheet1B11, activesheetB12, activesheetB13, activesheetB14

where activesheet is the sheet in which the formula resides

The correct representation of your 2nd average formula would be:
=AVERAGE(Sheet1!$B$11,Sheet1!$B$12,Sheet1!$B$13,Sheet1!$B$14)
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Dammit Zathras - beat me to it by about 10 seconds ;-) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

D'oh!

Apologies for besmirching the good name of Microsoft.

(Blush.)

 

[Zathras]

I'd have gotten there sooner, but it takes time to set up the workbook and do a thorough test.

 

[Stoffel24]

Hey guys, that was good on your part and bad on mine! You have answered my question and made one of my subsequent posts obsolete. Thanks! [Thumbsup2]