min amount formula exclude zero

Ian Bell
Ian Bell used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raheman M. AbdulMessaging and Directory Services
Commented:
try this
=SMALL(Q9:Q50000),COUNTIF($Q$9:$Q$50000,0)+1)

Open in new window


COUNTIF counts the zeros in the range plus 1 and informs SMALL to return the 'n'th smallest value.
Ian Bellretired

Author

Commented:
It works but if all the cells are zero it returns a #NUM error.
any suggestions ??
Ian
Raheman M. AbdulMessaging and Directory Services

Commented:
Hope you used =small((
2 brackets as I missed one earlier
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ian Bellretired

Author

Commented:
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
Try this.

=IFERROR(SMALL(Q9:Q200000,COUNTIF(Q9:Q200000,0)+1),"")
Top Expert 2016

Commented:
Could you precise this

and  determine the max number of rows.
Ian Bellretired

Author

Commented:
Sorry for the delay replying. The formula works well
Thank You guys
Ian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial