Average w/o Zero Values

[Laurie Forbes]

Was wondering if anyone could kindly suggest a method of determining
the average of a column (or row) of numbers but not including any
cells with a value of zero?

Also, if a range is conditionally formated to flag numerical values
not within a certain range, how can one prevent it from flagging cells
which are set = blank or empty?

TIA for any assistance.............


Laurie Forbes

 

[Norman Harker]

Hi Laurie:

For averages excluding zeros you can use:

=AVERAGE(IF(MyRange,MyRange))
Entered as an array by pressing Ctrl + Shift and then pressing Enter

With your conditional formatting problem.

Use a first conditional format with a formula of =ISBLANK(A1) but
don't set formats
Then use a second conditional format for formats not within your
range.

Excel takes the conditional formats and application of the first
condition excludes application of the second.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Laurie Forbes]

Hi Laurie:

For averages excluding zeros you can use:

=AVERAGE(IF(MyRange,MyRange))
Entered as an array by pressing Ctrl + Shift and then pressing Enter

Paul:

Thanks for replying to my query but I can't figure out what you mean
by the above - can you provide a little more detail please?

BTW, I've been thinking a little more about the problem and have
another alternative, I think:

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

With your conditional formatting problem.

Use a first conditional format with a formula of =ISBLANK(A1) but
don't set formats
Then use a second conditional format for formats not within your
range.

Excel takes the conditional formats and application of the first
condition excludes application of the second.

I think I didn't express the question properly. What I want to do is
avoid flagging a cell (with a conditional format) which would
othgerwise contain a number when the cell has been "blanked" (nothing
visible in the cell) by using ="" or = " " (by an IF statement) within
the cell's formula i.e. the cell is not cleared. If the cell is
actually cleared (by using edit or delete), the cell does not seem to
be flagged by the conditional format.

For example, if the conditional format says flag if the cell value is

2, a cell set to "" or " " will be flagged (which I don't want). The

same cell "cleared" by edit or delete will not be flagged. One way to
do it would be to set the condition to "between" 2 and 99999 (where
99999 is some arbitrary large number). That seems to work but seems
like a klutzy way of doing it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Laurie Forbes

 

[Laurie Forbes]

On Tue, 6 Jan 2004 13:18:11 +1100, "Norman Harker"

Sorry Norman - had a brain spasm and called you "Paul" in my previous
post.

Laurie Forbes

 

[Norman Harker]

Hi Laurie!

If the range of cells is A1:A100

=AVERAGE(IF(A1:A100,A1:A100))

On your other query, you don't need to set a top figure for >2.

With Cell Value > Greater than > 2 you'll only flag cells with values
greater than 2.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Laurie Forbes]

Hi Laurie!

If the range of cells is A1:A100

=AVERAGE(IF(A1:A100,A1:A100))

I still don't understand what you're getting at here Norman - the
above is equivalent to =AVERAGE(A1:A100) as the IF returns TRUE in
each case as it simply compares each cell with itself (or so it seems
to me).

On your other query, you don't need to set a top figure for >2.

With Cell Value > Greater than > 2 you'll only flag cells with values
greater than 2.

Not with Excell/Office 97 - maybe our versions differ.

--

Laurie Forbes

 

[Peo Sjoblom]

Laurie,

you have to enter the formula with ctrl + shift & enter

it works, it is also possible to write the formula as

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

 

[2rrs]

Was wondering if anyone could kindly suggest a method of determining
the average of a column (or row) of numbers but not including any
cells with a value of zero?

Also, if a range is conditionally formated to flag numerical values
not within a certain range, how can one prevent it from flagging cells
which are set = blank or empty?

TIA for any assistance.............


Laurie Forbes

And yet another one which is not an array formula;
excludes zeros and blanks.

=SUM(A1:A100)/MAX(1,COUNT(A1:A100)-COUNTIF(A1:A100,0))

 

[Laurie Forbes]

Laurie,

you have to enter the formula with ctrl + shift & enter

Peo:

Thanks - that works although I have no idea why. The formula comes
out:

{AVERAGE(IF(A1:A100,A1:A100))}

ie. braces at either end. Can you please explain what the {}s do?? I
can't find anything in Help to explain it.

it works, it is also possible to write the formula as

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

I tried that as it looked like it should work but, I get the same
result as without the "<>0". However, in the formula wizard box
(formula pallet??) (I don't know exactly what you call it - it's the
gray box that assists with composing a formula), the line at the
bottom that says "Formula result = X" gives the correct average.

IOW, the spreadsheet cell displayed average includes the "0" values
but the formula pallet displays the average *without* including "0"
values! In the example I used, the column contains only 10 values as
follows: 1, 1, 1, 0, 0, 0, 0, 0, 0, 5. The spreadsheet cell
containing the formula shows "0.8" while the formula pallet shows "2"
(the correct value) as the result.



Laurie Forbes

 

[Laurie Forbes]

And yet another one which is not an array formula;
excludes zeros and blanks.

=SUM(A1:A100)/MAX(1,COUNT(A1:A100)-COUNTIF(A1:A100,0))

Why not just use =SUM(A1:A100)/COUNTIF(A1:A100,"<>0") ? (as I
mentioned before, I came up with that after I posed the original
question).

Oh, wait, I just realized that your formula avoids a div by 0 if the
column contains all 0s.


Laurie Forbes

 

[Norman Harker]

Hi Laurie!

The braces { and } indicate that this is an array entered formula. Don
not attempt to put them in yourself.

This contrasts with arrays within formulas where you do enter the
braces.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.