MIN, MAX, AVERAGE functions

[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.

 

[Guest]

I would probably change the =B2/C2 to

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

however you can use

=AVERAGE(IF(ISNUMBER(D220),D220))

entered with ctrl + shift & enter

and

=MIN(IF(ISNUMBER(D220),D220))

(replace MIN with MAX to get MAX)


Regards,

Peo Sjoblom

 

[Helen Trim]

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

 

[Guest]

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

 

[Fredrik Wahlgren]

=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.

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