Adapt Formula to Ignore ZERO Value

[walkerl8]

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),"")

 

[James006]

Well,

If you go to Column H ... say in cell H50 and input the following array formula :

=MIN(IF(H27:H46>0,H27:H46))/A20

You will get ... ( I hope ...) what I am getting : 0.785

Is that right ???

P.S. Why are you referring in your formula to the Column K ...which is empty ... at least in my file ...

 

[James006]

By the way the Min (excluding 0 ...) in Column H is in cell H27 ... i.e 30.3 ... hence 30.3/38.6 = 0.785

Am I missing something ...???


P.S. $K$27:$K$42 ... not only you are using the Column K ... but only for the rows from 27 to 42 .... is there a reason ???

 

[walkerl8]

The 'K' column was me dragging data from the pivot to exclude the '0' values. If you copy the Counter Movement Data out of the pivot (Max should be 41.7 and Min should be 30.3) and run the percentrank.inc formula off a CMJ score of 38.6 - the outcome I get is 0.800.

If you look at scores like the Yo-Yo for example - I am getting 0.200 using percentrank.inc based on a score of 560.

 

[James006]

In my humble opinion, to be on the safe side....gain the flexibility you require and in order to add conditions the way you want, you are better off using Min or Max functions and dividing the result by the corresponding cell reference ...

In fact, this approach allows you to decompose the percentrank calculation ... and master the conditions you may want to add ...

For columns H and I :
Min : 0.785 and 0.786
Max: 1.080 and 1.500

Hope this will help

 

[walkerl8]

Sorry to keep dragging it on as I know its starting to get frustrating.

If I just summarise it really quickly and if you don't know then no problem.

You will see on 'Feedback Report' there is a dynamic pie chart the works off the 'Value' field within 'Raw Data (Charts). The reason I used PercentRank.Inc was because I needed a normalised value to get the pie chart to display an individuals score relative to the maximum for that test. So, therefore, if the player score for '10 M Sprint' was 1.50 and when looking in the range the best score was 1.50 then I would need to outcome value from the formula to be 1.00. That would be the case for everyone else, there scores marked against the data range and where that would fall on a normalised scale 0 - 1 (which would allow for the dynamic pie chart to be displayed relatively for every individual).

The score would need to take into account the type of test however, for speed tests the lower value is better and for things like jump tests, higher scores are better.

Does this make sense?

So as per your response about - a 1.500 is no good as I need it to be able to give me a value of between 0 and 1 like percentrank.inc would do if it didn't take 0s into consideration when looking at a range.

 

[James006]

Just did a bunch of tests ... with the percentrank function ...

It looks this function is totally insensitive to 0 and blanks ...

So it means that your end result is not affected by the zeros and the blanks ... since, with or without these rows, ...the percentrank function outputs the very same results ...

I guess the confusion came out with the heading in cell B1 ... percentrank does not produce the lowest ... :wink:

If you feel like taking a look at the percentrank interpretation :

Ranking Functions in Excel | Real Statistics Using Excel

Hope this will help

 

[FormR]

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?

=IFERROR(1-PERCENTRANK.INC(IF(B27:B100<>0,$B$27:$B$100),$A$14,3),"")

This works for me, are you sure you implemented it correctly?

Excel 2013
	A	B
1		Lowest
2	10 m Sprint (s)	1.000
3	30 m Sprint (s)	1.000
Raw Data (Charts)

Cell Formulas
Range	Formula
B2	{=IFERROR(1-PERCENTRANK.INC(IF($B$27:$B$100<>0,$B$27:$B$100),$A$14,3),"")}
B3	{=IFERROR(1-PERCENTRANK.INC(IF($C$27:$C$46<>0,$C$27:$C$46),$A$15,3),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James006]

Just did a bunch of tests ... with the Percentrank function ...

One can draw the conclusion the Percentrank function is totally insensitive to 0 and blanks ...

So it means that your end result is not affected by the zeros and the blanks ... since, with or without these rows, ...the Percentrank function outputs the very same results ...

All your results are correct ... from the very beginning ...!!!

I guess the confusion came out with the heading in cell B1 ... :wink:

Sorry for this ... my interpretation of Lowest ... was to get the Minimum ...

In addition, below is a link ... if you feel like taking a look at the Percentrank correct interpretation :

Ranking Functions in Excel | Real Statistics Using Excel

Hope this will help

 

[walkerl8]

Did you input this using the available workbook? I am sure I tried it and it didn't work but I will have to look again.

Thanks,

This works for me, are you sure you implemented it correctly?

Excel 2013

	A	B
1		Lowest
2	10 m Sprint (s)	1.000
3	30 m Sprint (s)	1.000

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Raw Data (Charts)​

Array Formulas Cell Formula B2 {=IFERROR(1-PERCENTRANK.INC(IF($B$27:$B$100<>0,$B$27:$B$100),$A$14,3),"")} B3 {=IFERROR(1-PERCENTRANK.INC(IF($C$27:$C$46<>0,$C$27:$C$46),$A$15,3),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[walkerl8]

So the highest score will always come out as the highest score (not necessarily 1.00 if there are 0 values in the range but none the less still the highest)

Just did a bunch of tests ... with the Percentrank function ...

One can draw the conclusion the Percentrank function is totally insensitive to 0 and blanks ...

So it means that your end result is not affected by the zeros and the blanks ... since, with or without these rows, ...the Percentrank function outputs the very same results ...

All your results are correct ... from the very beginning ...!!!

I guess the confusion came out with the heading in cell B1 ... :wink:

Sorry for this ... my interpretation of Lowest ... was to get the Minimum ...

In addition, below is a link ... if you feel like taking a look at the Percentrank correct interpretation :

Ranking Functions in Excel | Real Statistics Using Excel

Hope this will help