If statement then Average

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Renee - 20 Mar 2008 15:15 GMT

I have a column that has percentages in it; some have #'s great than zero
while others show a zero percentage. I would like my formula to say if the
cell = 0% then don't include in average calculation. Is this possible?

Thank you,
Renee

Reply to this Message

Mike H - 20 Mar 2008 15:54 GMT

Hi

=AVERAGE(IF(A1:A10>0,A1:A10,FALSE))

Extend the range to suit. It's an array so commit with
Ctrl+Shift+Enter

Mike

> I have a column that has percentages in it; some have #'s great than zero
> while others show a zero percentage. I would like my formula to say if the
> cell = 0% then don't include in average calculation. Is this possible?
>
> Thank you,
> Renee

Reply to this Message

David Biddulph - 20 Mar 2008 17:03 GMT

=AVERAGE(IF(B1:B100<>0,B1:B100,"")) as an array formula (Control Shift
Enter)

Signature

David Biddulph

>I have a column that has percentages in it; some have #'s great than zero
> while others show a zero percentage. I would like my formula to say if
[quoted text clipped - 3 lines]
> Thank you,
> Renee

Reply to this Message

Renee - 20 Mar 2008 17:48 GMT

Great thanks, that works. But now I have the autofilter on so when I select
one name I want to show the Average for just that name and not the average
for all the names. Is this possible?

> =AVERAGE(IF(B1:B100<>0,B1:B100,"")) as an array formula (Control Shift
> Enter)
[quoted text clipped - 5 lines]
> > Thank you,
> > Renee

Reply to this Message