+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    06-02-2013
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    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:

    Please Login or Register  to view this content.
    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

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    06-02-2013
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    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.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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?
    Audere est facere

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

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

    @daddylonglegs

    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

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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:
    Please Login or Register  to view this content.
    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?)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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
    Attached Files Attached Files

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

  12. #12
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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:
    Please Login or Register  to view this content.

    Offset version:
    Please Login or Register  to view this content.

    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).

  13. #13
    Registered User
    Join Date
    06-02-2013
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2007 : Wrong Result of Function PMT
    By owais.akber in forum Excel General
    Replies: 1
    Last Post: 01-10-2011, 11:26 PM
  2. formula giving wrong result sometimes?
    By lnjr in forum Excel General
    Replies: 2
    Last Post: 08-13-2010, 09:20 AM
  3. Replies: 3
    Last Post: 02-28-2006, 07:00 AM
  4. [SOLVED] giving points by a result (function)
    By Perttu O. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2005, 04:05 PM
  5. IF function giving wrong result
    By dzorug in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2005, 11:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1