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

 

[walkerl8]

Thanks James,

Link Below.

You will see on the 'Raw Data (Charts)' sheet in cell A2 (for example) - that formula is producing a value of 0.895 (this should be 1.00 as the score 1.57 is the fastest in the range) - I need to the formula in cell A2 to exclude 0's in the $B$27:$B$100 section.

Appreciate your time.

https://www.dropbox.com/s/ps495gvx393yrr2/PDP Physiological Test Data (Final Copy).xlsx?dl=0

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

Would you mind sharing your workbook ...

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

Cheers

 

[James006]

Thanks a lot for the file ...

Just had a look ... but it seems I cannot find the worksheet which is equivalent to the image you have posted ....

Is it the right file ...???

 

[walkerl8]

James,

Apologies,

Try this link instead

https://www.dropbox.com/s/5e0euyd1wunma8a/PDP Physiological Test Data (COMPLETED).xlsx?dl=0

 

[James006]

Thanks ...for the file ... :wink:

Will get back to you asap ...

 

[James006]

If in cell B2 ... you would test the following array formula ... (Control+Shift+Enter)

=MIN(IF($B$27:$B$46>0,$B$27:$B$46))/A14

Would that mean we are heading in the right direction ...???


If the answer is positive, in order to have a single formula you can copy all the way down... you would need to test the array formula

=IF(AND(A14<>0,A14<>""),MIN(IF(OFFSET($A$27:$A$46,0,ROWS($1:1))>0,OFFSET($A$27:$A$46,0,ROWS($1:1))))/A14,"")

Hope this is in line with your expectations ... :wink:

Cheers

 

[walkerl8]

Morning James,

Thanks for the reply.

Firstly, the following formula gives me exactly the value I am looking for (1.00 as the score being referenced in the best in the range. So we seem to be good there.

Secondly and ideally I would like a formula for each of those cells (B2:B10) - however, as you can probably see - things like speed are going to require a MIN aspect of the formula and things like the jumps etc are going to need a MAX. So I don't know if you have a way of building a formula to be able to populate that depending on the top of test. This isn't so much of an issue though as I can just paste the formula individually into each of those cells (B2:B10) as they won't move.

The other slight issue I can see is that you have made the formula look at the range B27:B46 (Data in the pivot), however, depending on the filters chosen, the pivot will either get smaller or bigger depending on the amount of data points. so for example if the pivot reduces to say B35 - we will encounter a value because of blank cells outside of the pivot. Alternatively, if the pivot grows to say B55 then a slight drag of the formula bar would be required. I know this is a basic thing but if there was a way to say have the formula look at B27:B500 for example and only just cells that house a value greater than 0 then that would be ideal.

Really appreciate you taking the time out though to have a look and come up with some solutions, I would be here for weeks if I didn't have the help of guys like yourself!

Hopefully hear from you soon

NB - I have just tried the a single array formula in cell B2 for 10 m sprint and that is coming up with 1.00 as well (which looks correct). I changed the cell range to A27:A100 and had no issues so am I right in thinking that irrelevant of whether the pivot grows or reduces that will work OK?

I will drag it down and take a look if it works for everything else in just a second.

 

[James006]

Glad it seems to be working out ...

Just a quick remark regarding the section : OFFSET($A$27:$A$46,0,ROWS($1:1) ...it actually means B27:B46 ...

since there is an Offset equal to 1 in the Column argument ...

Indeed Rows($1:1) has the advantage ... when copied down ... to increment itself ...

As a result with this argument, you are getting an automatic adjustment of "one more column to the right for each added row" ...

Hope this makes sense ...

 

[walkerl8]

Yeah I understand - but just to clarify:

- For the sake of the formula in B2 (10 m Sprint (s)) - that will work fine in terms of the pivot growing and reducing for that particular data range?
- If I want to now paste the same sort of formula into say cell B8 (Counter-Movement Jump) - would it be a case of changing the MIN to MAX and then moving the offset along to cells G27:G46 (SJ) in order to look across at CMJ?

Thanks again,

 

[James006]

May be there are distinct issues I do not understand ...

But if you copy the long version of the array formula :

=IF(AND(A14<>0,A14<>""),MIN(IF(OFFSET($A$27:$A$46,0,ROWS($1:1))>0,OFFSET($A$27:$A$46,0,ROWS($1:1))))/A14,"")

down to Cell B8 ... you will notice a result of 0.785 ... and within the formula an offset of 7 columns ...

which means the H column ...

Does it clarify ?

 

[walkerl8]

Yes that makes sense. However the value of 0.785 is slightly incorrect.

I just run the following formula on the data range H27:H46 (CMJ-No Arms (cm) (excluding 0s) and it returned 0.800 (which is correct).

The formula you have suggested needs to be MAX because I need to look at the score in cell A20 and compare against range H27:H46 at the highest value being the best value ('how high can you jump'). Conversely the MIN aspect of the formula you provided before works absolutely fine because it is speed data ('lower value = quicker' - hence why 1.57 =1.00 due to it being the fastest score within the range being looked at).

Apologies if I am missing something really simple

=IFERROR(PERCENTRANK.INC($K$27:$K$42,$A$20,3),"")

Thanks again
NB: I know that if you round the numbers up it goes to 0.8 which is correct - I am just wondering why percentrank.inc gives a value of 0.785 and the way we are looking into gives 0.800