Formula issue

[abombesposito]

Hey guys,

I am working on a excel sheet for work (cant share). This was created by another person. I a getting very inflated numbers when on the output. I tripled checked the table the If statement is running against and I see the data is correct. Please see below and attached.

Is this a data issue or forumula isssue?

=IF(ISERROR(VLOOKUP($A9,'BM Output'!$A$1:$L$218,6,FALSE)),"NA", VLOOKUP($A9,'BM Output'!$A$1:$L$218,6,FALSE))

 

[steve the fish]

You probably just need to divide the result by 100 but cant see the lookup table.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

If you have a newer version of Excel, your formula could be simplified to just this:

=IFERROR(VLOOKUP($A9,'BM Output'!$A$1:$L$218,6,FALSE),"NA")

Note that if your formula is returning some good values and some bad values, that would indicate that you have a data issue in your source data, where you have a mixture of different data types. So, as Steve the Fish said, you may need to divide some of the results by 100 (or better yet, correct the source data!).

 

[abombesposito]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

If you have a newer version of Excel, your formula could be simplified to just this:

=IFERROR(VLOOKUP($A9,'BM Output'!$A$1:$L$218,6,FALSE),"NA")

Note that if your formula is returning some good values and some bad values, that would indicate that you have a data issue in your source data, where you have a mixture of different data types. So, as Steve the Fish said, you may need to divide some of the results by 100 (or better yet, correct the source data!).

So its excel 2016 windows 10. Also I tried your forumal and it retruns the same inflated value. Below is what table its pointing to for data. I Snipped what i could.

 

[Joe4]

So its excel 2016 windows 10. Also I tried your forumal and it retruns the same inflated value.

That is fully expected. The formula I posted does exactly the same thing as the previous formula, only shorter.
The issue is with your data, not your formula.

Your screen print does not help much, as it does not appear to show the column that the data is being returned from (column G).
It is matching on column A, but returning the value from column G.
So we would need to see what your column G values look like.

 

[abombesposito]

That is fully expected. The formula I posted does exactly the same thing as the previous formula, only shorter.
The issue is with your data, not your formula.

Your screen print does not help much, as it does not appear to show the column that the data is being returned from (column G).
It is matching on column A, but returning the value from column G.
So we would need to see what your column G values look like.

Here is G

 

[Joe4]

Something doesn't look right.

If the value in column G of the matching row is 9.66, I don't see how it could possibly be returning 893.00%.
Is that VLOOKUP formula you are using (that we are talking about) realling in column M on that sheet?
Or is there a different formula in there?

Do either of those cells (the one with the 9.66 and the one with 893.00%) have any type of special formatting on them?

If neither of those things is true, something is off with your question.

 

[abombesposito]

Here is G

View attachment 42484

ColumnM is standard veviation. Maybe I will recreate with dummay data and same macro.

 

[steve the fish]

Like i said in post 2 you probably need to divide by 100 as 9 formatted to percentage is 900%. Also you arent looking for what is in column G. Your lookup is looking for the value in column A and the 6th column along, namely column F.

 

[spencer_time]

From looking at the data in your first post, and assuming the benchmark row is intended to be similar values to the rest, then I think that steve is correct, they all appear to be off by two orders of magnitude and dividing by 100 would at the very least be a quick fix (even if not the most elegant). Assuming the formula is correct as stated earlier in this thread, then I believe it would be an error in the formatting. I would look at the formatting of the cells in the benchmark row and the formatting in the other cells and ensure that they match each other.