Help with Averages/Sums of values returned from IF function

[TheBigUnit622]

Any help on this topic is appreciated, obviously.

I'm working in Excel, and I want to find an average of a range of 5 cells.
The thing is, that not all of these 5 cells have a value greater than 0, and
I only want the average of the cells greater than 0. On top of that, these
cells are all returned from a nested IF function that returns the values 0,
1, or 2 depending on the result of the IF's.

I'm guessing that because the value's of 0, 1, and 2 are returned from the
IF function are formatted as text, because I've tried to do an AVERAGE
function, and I've also tried to do a SUM combined with a COUNTIF(to count
those greater than 0, to assist with the average) and none of those are
returning anything. (The AVERAGE function returns a divided by 0 error, and
the SUM/COUNTIF both return 0 as a value).

I'm confident that this can be done, but I'm either over thinking it or just
plain don't know how to do what I'm trying to do. Any help is appreciated.

 

[TheBigUnit622]

I forgot to mention that this is on Excel 2003.

 

[Bernard Liengme]

Try =AVERAGE(IF(A1:A5>0,A1:A5))
but it is an array formula so you must commit it with CTRL+SHIFT+ENTER not
just ENTER. You will know if you did it correctly if you see that Excel has
added braces { } around your formula.

This non-array formula will also work =SUM(A1:A5)/COUNTIF(A1:A5,">0")

A third alternative is to change the IF formula. So, for example, rather
than =IF(A1<5, 0, A1*3) use
=IF(A1<5, "", A1*3). That is a pair of quotes (") in place of the zero.
Then simple AVERAGE formula will work for you

best wishes

 

[Bernard Liengme]

I have re-read your message! You say SUM(A1:A5) returns zero! Copy and Paste
one of your IF formulas in a reply to this message - it seems you are
getting text rather than numbers.

 

[Sheeloo]

Assuming your values are in A1:E1..

Try
=AVERAGE((A1:E1)*1)

IMP: Press CTRL-SHIFT-ENTER after typing/pasting as these are Array formulas.

It will give you average of 5 values and will count 0 as a value.

If you want average of non-zero values then use
=AVERAGE(IF((A1:E1)*1>0,(A1:E1)*1,""))

 

[TheBigUnit622]

Thanks for your help so far, Bernard. Seems like you know what I'm trying to
accomplish. One of the IF statements follows (Stats and Points is another
sheet in the workbook).

=IF('Stats and Points'!F2>=60, "2", IF('Stats and Points'!F2<=44.9, "0", "1"))

 

[David Biddulph]

Are you sure that you want text strings as the results of your formula? If
you want numbers, throw away the quote marks.

 

[TheBigUnit622]

Thanks to all three of you for the help. It's greatly appreciated.