Formula not VBA

[newk]

Please can someone look at my spreadsheet and tell me why Oct won't work. It's a simple formula, I've checked it a million times, but I can't see the problem.

Whilst viewing if anyone can make suggestions for improvements it would be much appreaciated.

Thank you

[Zack Barresse]

You have a space following the value in O14 ("Oct ").

Delete the space and you'll get 8.5. You can also shorten your formula to ...

=SUMPRODUCT(--(TEXT(D3:D25,"MMM")=O5),M3:M25)/SUMPRODUCT(--(TEXT(D3:D25,"MMM")=O5),--(M3:M25<>0))

If using this type of formula (barring the last argument is not screwed up - which will give you the #DIV/0 error) on error you will natively get the #N/A error (usually, not always), so no need to trap for it like you were in the IF statement. Depending on the circumstances, you may tend to get one error or the other.

Transversley you can use this array formula also ...

=AVERAGE(IF((TEXT(D3:D25,"MMM")=O5)*(M3:M25<>0),M3:M25))

Which should work just as fast (you won't notice the difference), but a little shorter. It must be confirmed with Ctrl + Shift + Enter instead of just Enter.

[newk]

Thanks so much dude, I would never have found that, you have reduced my blood pressure. My PC says thank you too as it can now be pulled back into the house.

Thanks for all the advise too, I will use it!

[Zack Barresse]

No problem! Glad you got it fixed (and are lessed of hypertension!).

Did you know (btw) that you can mark your own threads Solved? Go to Thread Tools --> Mark Solved --> Perform Action. (We thank Mark007 for that feature!)