# Using Offset function as the array in the PercentRank function is giving wrong result

1. ## Using Offset function as the array in the PercentRank function is giving wrong result

Hi gurus,

Long time reader, first time poster.

I'm having trouble using the offset function to define the range for the PercentRank function.

It looks like this: ``=PERCENTRANK(OFFSET('DIFOT Transaction Data'!\$A\$1,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0)-1,4,1,500),AE5)``
The formula is not returning an error, but it's returning a value lower than it should. The strangest part is, when I highlight the offset part of the formula and hit F9 to evaluate, and then hit enter, it returns the correct result.

So the offset function is providing something (no error), but not the right range (unless evaluated manually).

Does anyone know why this is happening, and if there are any workarounds?

Many thanks  Register To Reply

2. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Hi,

Are all your entries in the OFFSET range numeric? Any blank cells?

One possible explanation for the difference in results that you are experiencing is that your range contains some blank cells, which the PERCENTRANK function will ignore in its calculation. When you 'calculate' the OFFSET part of the formula, however, an array is generated in which blank cells are replaced by zeroes. When passed to the PERCENTRANK function, these zeroes are no longer ignored but form part of the calculation.

Having said that, without seeing your sheet I can't be sure why you are not getting the desired result. Can you post a small sample?

Regards  Register To Reply

3. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Thanks XOR that's very interesting. Many cells in my range are blank, so that may be the cause, although I'm still a bit unsure of why executing the same formula in two ways will yield different results.

My current workbook is large and filled with costs and prices, but I'll try to build a little sample with dummy data when I get to work tomorrow morning. I most appreciate your help.  Register To Reply

4. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Your formula works OK for me when I tested, even including blanks in the range. Your OFFSET function is returning a horizontal range (a row) of 500 cells starting at column E for the relevant row, is that your intention?  Register To Reply

5. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Can you elaborate on my explanation as to why 'calculating' the range in the formula bar in this case (or in general) returns an array in which the blanks are replaced by zeroes? Is this documented?

Agree with the OP that it seems an undesirable feature that repeated calculation in the formula bar can lead to a different result from that obtained from calculating the cell.

Regards  Register To Reply

6. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

I agree with your assessment but that can happen with many formulas, especially where blanks might become zeroes, e.g. if A1 and B1 are blank cells and I use the formula

=LARGE(A1:B1,1)

then I get the result #NUM! as expected because there are no numbers in the range......but if I select A1:B1 in formula bar and press F9 I see {0,0}. You can't expect the results to be identical once you press F9 because you are potentially doing a different calculation.  Register To Reply

7. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Thanks a lot. Interesting, though, as it's not clear:

a) what that "calculation" is (that converts a blank cell to a numeric 0)

b) how this process differs from that in e.g. the Evaluate Formula dialog, in which ranges containing blanks are returned as just that ("") before passing to the function.

Regards  Register To Reply

8. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

I'm not 100% that this will 'fix' anything, but here's the same formula rewritten to use INDEX instead of OFFSET: ``=PERCENTRANK(INDEX('DIFOT Transaction Data'!\$E:\$SJ,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0),0),AE5)``
Testing that had proper results for me, even including blanks (which were ignored and not converted to 0. Should blanks be converted to 0 for this?)  Register To Reply

9. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

@tigeravatar

The issue is not that blanks are converted to 0 in the actual formula calculation (they are not).

It was more that I (and the OP, I hope!) have become interested in the secondary issue of the difference in results between that given by the formula and that obtained through repeated use of partial calculation using F9 in the formula bar.

Have you tested whether this difference exists in your INDEX version (with blanks in the range, of course)?

Regards  Register To Reply

10. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Attached is an example workbook based on the criteria described.
It contains both the Offset and Index formulas.
On my machine, both give identical results and I am unable to find any anomalies when calculating in the address bar or using the evaluate formula tool. Sorry I'm not more help on this   Register To Reply

11. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Hi tigeravatar,

Are you sure? I just repeated the experiment with your first formula in cell E5:

Current formula result: 0.573 (correct)

In formula bar, highlight OFFSET portion of formula only, press F9 (returns array including zeroes where blanks exist), press ENTER; result 0.777.

The latter is due, as I have stated, to the fact that zeroes are now being passed to the PERCENTRANK formula whereas previously, since these were blank, they were excluded from the calculation.

Regards  Register To Reply

12. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Oh interesting! I was highlighting the whole formula, not just the offset/index portion. I do get those different results where the blanks are being converted to 0's. That happens for both formulas.
If the blank cells are supposed to be calculated as 0's, you can tell the formula to do so by adding another index to convert blanks, like so:

Index version: ``=PERCENTRANK(INDEX(--(0&INDEX('DIFOT Transaction Data'!\$E:\$SJ,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0),0)),),AE5)``

Offset version: ``=PERCENTRANK(INDEX(--(0&OFFSET('DIFOT Transaction Data'!\$A\$1,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0)-1,4,1,500)),),AE5)``

Both of those adjusted formulas will return the result 0.777 (which is the result when calculating blanks as 0s).
So basically, the OP needs to choose whichever version s/he needs (calculate blanks as 0's or ignore blanks).  Register To Reply

13. ## Re: Using Offset function as the array in the PercentRank function is giving wrong result

Ok so this is quite fascinating.

What Ive learnt from above help from you guys is that PercentRank ignores blanks. Offset turns blanks into 0s. But whats interesting to me is that when the offset function is used to define the array within another function, it is not evaluated, it simply points that function to the right range, which the parent function then evaluates.

So the offset function operates one way when evaluating a range (with F9), and another way when nested in another function. Which is useful, and kind of obvious in hindsight.

So thanks for explaining that, it all makes sense to me now.

I thought some of you may be interested in what was driving the problem:

The purpose was to 1. evaluate how often (as a %) our stock holdings were meeting customer requirements, and then 2. to calculate what stock level was required to meet our desired level of service. Percentrank is being used to calculate current performance, and Percentile is being used to calculate stock required to meet desired service level.

In my application, I initially wanted the blanks to be treated as 0s. For me, those were sales days where the stock holding was adequate to fulfil deliveries (obviously, no sales means any stock level is adequate). Because there were many days where there were no sales, I was expecting a high percentrank result, which I obviously wasnt getting (without forcing offset to evaluate first). This is why I was questioning the formula. But, on reflection, the lower figure is probably the more useful one to use, otherwise we are kidding ourselves about our performance.

Perhaps in years to come this will come up in a google search, so Ive attached the example for posterity. Its an extremely simple but useful DIFOT measurement tool, and improvement model.

Thanks again for all your help.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 