MIN, MAX, AVERAGE functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

=This is a great forum - I've had several tough questions (for me anyway)
answered. I have yet another one.

In two adjacent columns, I have data which will be divided against each
other to get an average. The average is reported in a 3rd column, which is
formatted as a percentage. The function in each row is similar - =B2/C2;
=B3/C3, etc.

At the bottom of the 3rd column, I want to calculate a MIN, MAX, and AVERAGE
of all viable data points.

Since empty rows come up as #DIV/0 (can't divide by zero, I suppose), the
only values I'm getting are #DIV/0.

What kind of functions do I need and where in order to skirt this problem?

Many thanks.
 
I would probably change the =B2/C2 to

=IF(C2="","",B2/C2)

however you can use

=AVERAGE(IF(ISNUMBER(D2:D20),D2:D20))

entered with ctrl + shift & enter

and

=MIN(IF(ISNUMBER(D2:D20),D2:D20))

(replace MIN with MAX to get MAX)


Regards,

Peo Sjoblom
 
Change the calculation B2/C2 to:

if(OR(B2="",C2=""),"",B2/C2)

Then if either cell is empty, the calculation cell is left
empty. Then the min, max and aqverage ignore the empty
cells.

HTH
Helen
 
You could try this formula instead of =B2/C2:

=IF(ISERROR(B2/C2),"",B2/C2)

This will give you an empty text string instead of an error message, and I
think it will allow you Min, Max functions to operate.

Nige
 
Allen7575 said:
=This is a great forum - I've had several tough questions (for me anyway)
answered. I have yet another one.

In two adjacent columns, I have data which will be divided against each
other to get an average. The average is reported in a 3rd column, which is
formatted as a percentage. The function in each row is similar - =B2/C2;
=B3/C3, etc.

At the bottom of the 3rd column, I want to calculate a MIN, MAX, and AVERAGE
of all viable data points.

Since empty rows come up as #DIV/0 (can't divide by zero, I suppose), the
only values I'm getting are #DIV/0.

What kind of functions do I need and where in order to skirt this problem?

Many thanks.
Click to expand...


Use a help column with the formula =IF(ISNUMBER(C1),C1,"")
You can now use Min, Max and Average on this column, #DIV0 values will be
completely ignored.

/Fredrik
 
Back
Top