Link to home
Create AccountLog in
Avatar of Ian Bell
Ian BellFlag for United Kingdom of Great Britain and Northern Ireland

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
Thanks for your help
Ian
SOLUTION
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ian Bell

ASKER

It works but if all the cells are zero it returns a #NUM error.
any suggestions ??
Ian
Hope you used =small((
2 brackets as I missed one earlier
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
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rgonzo1971
Rgonzo1971

Could you precise this

and  determine the max number of rows.
Sorry for the delay replying. The formula works well
Thank You guys
Ian