Adapt Formula to Ignore ZERO Value

walkerl8

Board Regular
Joined
Apr 30, 2013
Messages
201
Hi All,

Does anyone know what additions I need to make to the following formula in order for the range that is being looked at to ignore any 'Zero' value?

Thanks in advance,

=IFERROR(1-PERCENTRANK.INC($B$27:$B$100,$A$14,3),"")
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

It seems to me that in order to insert your condition :$B$27:$B$100>0 ...

you will have to use the sumproduct() function ...

HTH
 
Upvote 0
Does this work?

=IFERROR(1-PERCENTRANK.INC(IF(B27:B100<>0,$B$27:$B$100),$A$14,3),"")
This now makes it an array formula
Array formula, use Ctrl-Shift-Enter
 
Upvote 0
I replaced my formula with your recommendation (just to clarify my formula is housed in cell B2) and when I hit Control - Shift - Enter (or just Enter) - the cell goes blank?

Does this work?

=IFERROR(1-PERCENTRANK.INC(IF(B27:B100<>0,$B$27:$B$100),$A$14,3),"")
This now makes it an array formula
Array formula, use Ctrl-Shift-Enter
 
Upvote 0
James would be you able to talk me through using sumproduct() within the formula I provided before?

Thanks,


Hi,

It seems to me that in order to insert your condition :$B$27:$B$100>0 ...

you will have to use the sumproduct() function ...

HTH
 
Upvote 0
Sorry if I did not express myself properly ...

I meant replace the Percentrank function by its sumproduct() equivalent ...

Without your worksheet, my guess would be you should test the following

Code:
=(SUMPRODUCT(($B$27:$B$100<A14)*($B$27:$B$100>0))/(SUMPRODUCT(($B$27:$B$100<A14)*($B$27:$B$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100

HTH
 
Upvote 0
No problem James,

I need to utilise something like Percentrank as I am using the data to populate a dynamic pie chart. I just tried to enter your fomula and it throws some value out really wide of the mark.


Sorry if I did not express myself properly ...

I meant replace the Percentrank function by its sumproduct() equivalent ...

Without your worksheet, my guess would be you should test the following

Code:
=(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))/(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100

HTH

</a14)*($b$27:$b$100></a14)*($b$27:$b$100>
 
Upvote 0
James,

Below is a link to an image to show you the sheet I am working on - if you need the workbook then I will upload that for you. The formula I am trying to edit is housed in cell B2 where as you can see at the moment it is coming up with a value of 0.895. This value should be 1.000 however it is 0.895 because the formula we are discussing is including '0' values into the percentrank.inc function. The percentrank.inc function is allowing my testing data to be referenced relatively between a minimum and a maximum value in a particular range which is facilitating the display of my data on a dynamic pie chart on another sheet.

So in simple speak - I need this formula
Code:
[COLOR=#333333]=IFERROR(1-PERCENTRANK.INC($B$27:$B$100,$A$14,3),"")[/COLOR]
to work EXCLUDING any '0' values.

You can see the '0's are housed within the '10 m (Fastest) (sec)'

Thanks for your time,


https://www.dropbox.com/s/ie4hzb9a26qra56/IMAGE.JPG?dl=0



Sorry if I did not express myself properly ...

I meant replace the Percentrank function by its sumproduct() equivalent ...

Without your worksheet, my guess would be you should test the following

Code:
=(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))/(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100

HTH

</a14)*($b$27:$b$100></a14)*($b$27:$b$100>
 
Upvote 0
Does anyone have any solutions?

Would be so so grateful

Had the impression the Sumproduct() formula could be a solution ...:confused:

Would you mind sharing your workbook ...

I will try to help you out ... to the extent of my possibilities ...:wink:

Cheers
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top