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

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

3. 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 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. XP, Can you change the formula in I 13 from

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

to

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

Maud

5. 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
•