• Index match paradox

    Author
    Topic
    #500353

    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

    Viewing 1 reply thread
    Author
    Replies
    • #1508820

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

      • #1508831

        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

    • #1508832

      XP, Can you change the formula in I 13 from

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

      to

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

      Maud

      • #1508847

        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)

    Viewing 1 reply thread
    Reply To: Index match paradox

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: