Hello there!

I wonder if you can help me in this question. I have a MIN function which calculates the smallest value in a list of percentages and then divides each percentage by the smallest in the list. It works fine until one of the percentages becomes zero with the resulting division-by-zero error. Do you think it is possible to make the MIN function to exclude all instances (there may be more than one zero) of zeroes from its calculation? I have attached the spreadsheet.

Thank you for your time,

D

Hi

You could use a formula posted recently by Harlan Grove.
Instead of using the Minimum value to divide by, you use the Small
function, taking the smallest non zero value

=A1/(SMALL(A:A,COUNTIF(A:A,"=0")+1))

--
Regards

Roger Govier


"zealot" wrote in message
...

Hello there!

I wonder if you can help me in this question. I have a MIN function
which calculates the smallest value in a list of percentages and then
divides each percentage by the smallest in the list. It works fine
until one of the percentages becomes zero with the resulting
division-by-zero error. Do you think it is possible to make the MIN
function to exclude all instances (there may be more than one zero) of
zeroes from its calculation? I have attached the spreadsheet.

Thank you for your time,

D


+-------------------------------------------------------------------+
|Filename: division by zero.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=84|
+-------------------------------------------------------------------+



--
zealot

thanks!!!!

I have an additional question…do you know how to make this formula to give off a zero if all the FILLED cells in a list are equal to 0. for example

When the list is as follows

0%
0%
0%
0%

Or

0%
0%
0%

or

0%

Thanks a lot!!!!!!!!!!!!!!

Hi
Try
=IF(COUNTIF(A:A,"=0")=COUNT(A:A),0,A1/(SMALL(A:A,COUNTIF(A:A,"=0")+1)))
--
Regards

Roger Govier


"zealot" wrote in message
...

thanks!!!!

I have an additional question…do you know how to make this
formula to give off a zero if all the FILLED cells in a list are equal
to 0. for example

When the list is as follows

0%
0%
0%
0%

Or

0%
0%
0%

or

0%

Thanks a lot!!!!!!!!!!!!!!


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
zealot

thanks !!! i will check that!