Getting a blank of a "0" instead of #NUM

[Sam B]

Hi

I have this formula for calculating the percentile from a huge table

=PERCENTILE(IF(((List!$AG$2:$AG$10000="Evaluation")+((List!$AG$2:$AG$10000="Hold")*(List!$D$2:$D$10000<>"H")))*(List!$C$2:$C$10000="US"),List!$BH$2:$BH$10000 ),0.95)

This works great. However, if there are no entires that match the listed
criteria (which is a valid option) I get a #NUM instead of a blank or a 0.
All the values show up in a master table where I use conditional formating
(red, yellow, and green) and if the value returned is #NUM then it shows up
as #NUM in the final table.

In a nutshell, I want to get rid of #NUM. Appreciate any help on this.

 

[Gaurav]

This should give you a 0

=IF(ISERROR(PERCENTILE(IF(((List!$AG$2:$AG$10000="Evaluation")+((List!$AG$2:$AG$10000="Hold")*(List!$D$2:$D$10000<>"H")))*(List!$C$2:$C$10000="US"),List!$BH$2:$BH$10000
),0.95)),0,(PERCENTILE(IF(((List!$AG$2:$AG$10000="Evaluation")+((List!$AG$2:$AG$10000="Hold")*(List!$D$2:$D$10000<>"H")))*(List!$C$2:$C$10000="US"),List!$BH$2:$BH$10000 ),0.95)))Thanks"Sam B" <[email protected]> wrote in messageHi>> I have this formula for calculating the percentile from a huge table>>=PERCENTILE(IF(((List!$AG$2:$AG$10000="Evaluation")+((List!$AG$2:$AG$10000="Hold")*(List!$D$2:$D$10000<>"H")))*(List!$C$2:$C$10000="US"),List!$BH$2:$BH$10000 ),0.95)>> This works great. However, if there are no entires that match the listed> criteria (which is a valid option) I get a #NUM instead of a blank or a 0.> All the values show up in a master table where I use conditional formating> (red, yellow, and green) and if the value returned is #NUM then it showsup> as #NUM in the final table.>> In a nutshell, I want to get rid of #NUM. Appreciate any help on this.>>

 

[Sam B]

Thank you. It works!

 

[Gaurav]

Glad it worked!!

Thank you. It works!

 

[sivak]

Try adding a ZERO to the calculated value.

= 0 + (PERCENTILE(A3:A6,0.3))