Results 1 to 5 of 5
Thread: Index match paradox

20150607, 22:27 #1
 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

20150608, 01:29 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 30
 Thanked 61 Times in 57 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; 20150608 at 01:34.

20150608, 06:00 #3
 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.
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 12 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?
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?
Couldn't you also use ROW() rather than ROW(1:1) ?
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

20150608, 06:18 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
XP, Can you change the formula in I 13 from
=IFERROR((D13$H13)*100/$D13,"")
to
=IFERROR((D13$H13)*100/$D13,0)
Maud

20150608, 08:58 #5
 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)
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 "reset" 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)