Eliminating the zeroes from the calculation

Z

zealot

Hello there!

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

Thank you for your time,



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

Roger Govier

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))
 
Z

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!!!!!!!!!!!!!!


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
R

Roger Govier

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

zealot

thanks !!! i will check that!






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

Roger Govier


"zealot" [email protected] 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!!!!!!!!!!!!!!


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
Click to expand...


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
Top