MIN, MAX, AVERAGE functions
 

=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

"Allen7575" wrote:

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

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


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


.

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" wrote:

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

"Allen7575" wrote in message
...
=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