Average, IF

[Guest]

I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

 

[Guest]

Hi,

I'm sorry but i dont think i understand your question properly. If you want
to average all values greater than 0, use the following array formula
(Ctrl+Shift+Enter)

=average(if((range>0),range))

Please pardon me if my interpretation of your question is incorrect. You
may mail me if you need any further clarifications.

Regards,

Ashish Mathur
India

 

[Bob Phillips]

Just use

=AVERAGE(IF(A1:A3<>"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<>0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

 

[Don Guillett]

try adding another parameter
rngB>0

 

[Guest]

Thanks Ashish, would you see my reply to Bob's suggestion and see if this
helps to clarify the question?
Thanks.

 

[Bob Phillips]

I anticipated that by offering the alternative of

=AVERAGE(IF(A1:A3<>0,A1:A3)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Hi!

I want to wait to perform the average
until either a whole number or a 0 are entered.

=IF(COUNT(A1:A3)<3,"",AVERAGE(A1:A3))

Biff

 

[Ragdyer]

Does this work for you:

=IF(COUNT(A1:A3)=3,AVERAGE(A1:A3),"Missing Info")
?

 

[Guest]

Could you give me the full formula you're suggesting?
Thanks!

 

[Guest]

Sorry Bob, but they both seemed to work the same.
If A3 (March) is blank (nothing entered in the cell) I want NO average
returned.
If A3 March has any number, including 0 I want the average for Jan, Feb,
March returned.
Thanks for your patience!

 

[Guest]

BINGO!
That got it.
Thanks everyone for your help.
Now, any suggestions for easily updating this formula into my worksheet in
60+ different areas?

Thanks again!