Average if based on month in date string - Formula not working

[Badvgood]

Hi all,

Please see the attached. I can't fathom out why the formulas in N7 to N17 fail to work (Jan to Nov) but N18 (Dec) works.

Any pointers would be great.

Thanks

[Pete_UK]

You only have data for December and January, but the formula for January has not been entered using Ctrl-Shift-Enter. Put this formula in N7 instead:

=AVERAGE(IF(MONTH($A$2:$A$394)=ROWS($1:1),$C$2:$C$394))

Commit it by holding down the Ctrl and Shift keys together, then tapping on Enter. Then you can copy the one formula down (without having to explicitly include the month numbers).

Hope this helps.

Pete

[Badvgood]

Hi,

Thanks for the speedy response. I have done this but the averages dont seem to averaging, they are incorrect.

IE for Jan I would expect 6.9 but I'm getting 3.3.

Any ideas?

[Pete_UK]

That's because the formula is counting 31 days in January. Use this instead:

=AVERAGE(IF((MONTH($A$2:$A$394)=ROWS($1:1))*($C$2:$C$394<>""),$C$2:$C$394))

Commit using CSE, then copy down.

Hope this helps.

Pete