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]

In the link provided ... the best example is located in Figure 7 ...

Since you are using this function ... the correct interpretation is essential ...!!!

 

[FormR]

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.

Yes, and it worked perfectly.

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

It is not insensitive to zeros.

Excel 2013
	A	B
1	0.5	0.75
2	1	1
3		0
4	3	3
Sheet1

Cell Formulas
Range	Formula
A1	=PERCENTRANK.INC(A2:A4,2)
B1	=PERCENTRANK.INC(B2:B4,2)

 

[James006]

@ FormR

You have tested PERCENTRANK.INC() function ... whereas my test was about PERCENTRANK() function ...

Thanks for spotting they behave differently ...

 

[FormR]

whereas my test was about PERCENTRANK() function ...

Thanks for spotting they behave differently ...

Hi, still not insensitive to zeros:

Excel 2013
	A	B
1	0.5	0.75
2	1	1
3		0
4	3	3
Sheet1

Cell Formulas
Range	Formula
A1	=PERCENTRANK(A2:A4,2)
B1	=PERCENTRANK(B2:B4,2)

 

[James006]

Hi, still not insensitive to zeros:

Excel 2013

	A	B
1	0.5	0.75
2	1	1
3		0
4	3	3

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

Sheet1​

Just noticed Excel 2013 ... Would you have the possibility to perform your tests with Excel 2007 ?

 

[walkerl8]

Morning,

You are right it does work - I must have hit a typo of some sort.

Last thing from me if possible - do either of you know if I can get this formula to work with the addition of an offset - similar to the work James produced with the MIN function. Like I said my pivot table will potentially increase and decrease so if it could work with an offset feature then that would be awesome. This is more of an efficiency and formatting aspect that anything else but thought I would ask the question.

Thanks both,

Yes, and it worked perfectly.



It is not insensitive to zeros.

Excel 2013

	A	B
1	0.5	0.75
2	1	1
3		0
4	3	3

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

Sheet1​

Worksheet Formulas Cell Formula A1 =PERCENTRANK.INC(A2:A4,2) B1 =PERCENTRANK.INC(B2:B4,2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[FormR]

Just noticed Excel 2013 ... Would you have the possibility to perform your tests with Excel 2007 ?

I don't have XL2007 - are you saying that you get a different result with the simple set-up in post#34 with that version? Maybe another reader with that version can check.

do either of you know if I can get this formula to work with the addition of an offset

Try doing a search for "Excel dynamic named ranges" - and use the named range in place of the cell references.

 

[walkerl8]

Will do,

Thanks

I don't have XL2007 - are you saying that you get a different result with the simple set-up in post#34 with that version? Maybe another reader with that version can check.



Try doing a search for "Excel dynamic named ranges" - and use the named range in place of the cell references.