Average is returning a wrong answer

[frenchfry]

I have a pretty complicated sheet that is calculating the average for the last 3 days, 6, 12 and 24 days. I have noticed that the answers fro 3 & 6 days are correct, but the 12 & 24 days are not correct.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-3,16,3)) is correct answer!
[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-6,16,6))

is correct answer!

These are not correct
​

[/FONT]<strike></strike>

=AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-12,16,12))
​

​

<strike></strike>

=AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-24,16,24))


the formula only counts cells with numbers in them. I have checked and there are no zeros, the formatting is for numbers. I am stumped as to why this is happening. Any help would be appreciated​

 

[pgc01]

Hi
Welcome to the board

...the formula only counts cells with numbers in them. I have checked and there are no zeros, the formatting is for numbers...

Excel won't care about formats when performing the calculations.

Confirm that you have only number values.

When the formula

=AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-12,16,12))

does not work ok, use in another cell:

=COUNT(AVERAGE(OFFSET(C1,0,COUNT(C2:ZY2)-12,16,12)))

and check that it's 192.