Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Index match paradox

    I'm not sure where the error is, or missing something in the INDEX MATCH formula.


    Only happens when there is a zero anywhere in the array of numerical values.

    percent calculation, the sum was rightfully zero, Range B7.
    and
    percent calculation, the sum was rightfully (0.08), Range F6.


    But in Cell Range (I13) I get a blank when calculating for percent variance


    Therefore in the result columns, K and L I get the same number twice.

    How can this "paradox", for lack of better terms be corrected ?

    Thanks
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    When you divide by D13 (which is a 0 in your sheet), this is an error so you get "" in the cell.

    Also, the SMALL function compares numeric values. If you have a blank in the range, the comparison will return an error which you than turn into a "".
    What do you want it to be?

    Why wouldn't you use VLOOKUP rather than index & match? Couldn't you also use ROW() rather than ROW(1:1) ?
    Last edited by kweaver; 2015-06-08 at 02:34.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    kweaver
    When you divide by D13 (which is a 0 in your sheet), this is an error so you get "" in the cell.
    Thanks for the reply,

    Overall, the sheet is not "static", the entire workbook is not static either, it is real time web query.
    The updated numerical values may occur from every 30 seconds to 1-2 minutes, depends.
    It only updates because there was a change. Meaning, it may query several times within the minute and receive the exact same data, each query is processed to calculate if there was a change. If there is a change, it does the next thing. Hence the titles in Rows 17 and 18.


    The Value in D13 is not actually an error, it is a previous calculation from 2 sets of data and doing a percent comparison of those 2 sets, yet the item number/s corresponding in A1 are relevant overall
    If both sets of data are exactly the same value, it becomes a 0, and that's fine as an indicator,
    there was no change from one update to the next during the web query on that particular item, in this case 13.



    Also, the SMALL function compares numeric values. If you have a blank in the range, the comparison will return an error which you than turn into a "".
    What do you want it to be?
    Yes, I thought so to, a choice has to be made.
    The reason it's "" is because it leaves the cell blank and looks neater rather than have unnecessary clutter.





    Why wouldn't you use VLOOKUP rather than index & match?
    Not sure how VLOOKUP work in this case.


    Couldn't you also use ROW() rather than ROW(1:1) ?
    I used the same formula from another sheet I learnt here on this forum.
    I am not sure why the ROW() is there either other than it works and every time I change things it messes up.
    So I left it as is, it seems to work.
    ---------------
    In conclusion, not every web query update of real time data calculates a 0.

    I just thought if there is a way to over come this not so often glitch, I'd ask.

    Thanks

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    XP, Can you change the formula in I 13 from

    =IFERROR((D13-$H13)*100/$D13,"")

    to

    =IFERROR((D13-$H13)*100/$D13,0)

    Maud

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Maudibe
    XP, Can you change the formula in I 13 from

    =IFERROR((D13-$H13)*100/$D13,"")

    to

    =IFERROR((D13-$H13)*100/$D13,0)
    Thanks,

    The workaround I think is to use some VBA, to remove formulas if data rows are blank.
    There is usually a Minimum of 7 to a max of 16, in this case it was 15 items in the example sheet.
    Then "re-set" the formula using some VBA

    If I correct the formula on that entire column ( from to $D to $H)
    =IFERROR((D13-$H13)*100/$H13,0)
    then it puts the 13 at the very top, showing (100)

    It's preferred in the middle, but I really don't know what is correct at this stage till some tests are done over time.

    Is it
    =IFERROR((D13-$H13)*100/$D13,0)
    or
    =IFERROR((D13-$H13)*100/$H13,0)

Posting Permissions

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