Creating If Statement not to include if O in average

[C Kreig]

Good afternoon!
I have a spreadsheet that includes all 12 month, however when it figures the
yearly average I do not want it to include it when it does the average. I
have posted the formula below. Can you please help me figure this out.
=IF(C10=0,"0",AVERAGE(C10:N10))
Thank you in advance for your help.

 

[Mike H]

Hi,

You may mean this

=AVERAGE(IF(C10:N10<>0,C10:N10,FALSE))

This will average c10:N10 excluding zero.
It's an array which must be entered with CTRL+Shift+Enter. If you do it
correctly Excel will put curly brackets around the formula {}. You can't type
these yourself.

Mike

 

[Sandy Mann]

I read it that you are trying to exclude months that have a return of zero
or months that are still in the future. If so try:

=IF(C10=0,0,SUM(C10:N10)/COUNT(C10:N10))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[C Kreig]

Mike-
Thank you for your help, however I did this and now I get the #Value.
Please advise.

 

[Mike H]

Read my first reply again and enter the formula as an Array

Mike

 

[C Kreig]

Sandy-
It is not giving me the appropriate average of the months that have data.
Jan - 127
Feb - 139
Mar - 157
Apr - 145
May - 125
Jun - 124
Jul - 123
Aug - 128
Sept - 0
Oct - 0
Nov - 0
Dec - 0
Year to date total is 1,068
Year to date Average is 133.50
With the formula you gave me it gives me an average of 89.

 

[Mike H]

Modify Sandy's formula to this

=IF(C10=0,0,SUM(C10:N10)/COUNTIF(C10:N10,">0"))

Mike

 

[C Kreig]

Mike-
You rock! Once I figured out the Array it worked!!

 

[C Kreig]

Mike-
I am running into issues on the bottom of the spreadsheet.
I am now pulling the data by quarters and it does not like the Zeros
I am now getting the #DIV/0!
1st Quarter 141 (Jan-Mar Data) =AVERAGE(IF(C10:E10<>0,C10:E10,FALSE))
2nd Quarter 131 (Apr - Jun Data) =AVERAGE(IF(F10:H10<>0,F10:H10,FALSE))
3rd Quarter 126 (Jul - Sept Data) =AVERAGE(IF(I10:K10<>0,I10:K10,FALSE))
4th Quarter (all zeros) =AVERAGE(IF(L10:N10<>0,L10:N10,FALSE)) (ERROR
message #div/0!)
Year to date: =AVERAGE(IF(B32:B35<>0,B32:B35,FALSE)) (ERROR message #div/0!)