Ian Bell

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

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(F

I have set to a maximum of 50,000 rows

Thanks for your help

Ian

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Hope you used =small((

2 brackets as I missed one earlier

2 brackets as I missed one earlier

ASKER

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

It has 2 opening and two closing brackets

=SMALL(Q9:Q200000,COUNTIF(

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Could you precise this

and determine the max number of rows.

ASKER

Sorry for the delay replying. The formula works well

Thank You guys

Ian

Thank You guys

Ian

ASKER

any suggestions ??

Ian