  asked on

# min amount formula exclude zero

Hi

I would like to modify the following formula to calculate the minimum value in a range of cells exclude zeros and
determine the max number of rows.
=SMALL((Q9:Q50000),INDEX(FREQUENCY((Q9:Q50000),0),1)+1)
I have set to a maximum of 50,000 rows
Ian
Microsoft Excel Last Comment
Ian Bell

8/22/2022 - Mon
SOLUTION
Raheman M. Abdul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Ian Bell

It works but if all the cells are zero it returns a #NUM error.
any suggestions ??
Ian
Raheman M. Abdul

Hope you used =small((
2 brackets as I missed one earlier
Ian Bell

This is the formula you provided.
It has 2 opening and two closing brackets

=SMALL(Q9:Q200000,COUNTIF(Q9:Q200000,0)+1)

If all the cell contents contain zero then it posts the #NUM error
Excel amusant

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Rgonzo1971

Could you precise this

and  determine the max number of rows.
Ian Bell