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

 

[Joe4]

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.

Boy, that was good counting by me!
Of course, column F is the 6th column, not column G!


Even so, that would return 23.39, which isn't anything like 893.00%. So there is some disconnect or important detail being left out.
It may be best to dummy up the data and upload a copy of your workbook to a file sharing site for us to download.

 

[abombesposito]

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.

Thanks, Spencer, I checked the formatting of the cell, and looks fine. I cloned the data and removed any sensitive info. Please see below:

Test Macro.xls

drive.google.com

 

[abombesposito]

Boy, that was good counting by me!
Of course, column F is the 6th column, not column G!


Even so, that would return 23.39, which isn't anything like 893.00%. So there is some disconnect or important detail being left out.
It may be best to dummy up the data and upload a copy of your workbook to a file sharing site for us to download.

See latest post. I included cloned data.

 

[Joe4]

OK, you did make a typo in your formula, which is what made it so confusing.
You said your formula is:

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

but it is actually:

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

So you are actually returning values from the 9th column, not the 6th.

The values in the 9th column are not percentages, so you have to divide by 100 (as previously suggested) if you want them to be.
So you could use this formula:

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

 

[abombesposito]

OK, you did make a typo in your formula, which is what made it so confusing.
You said your formula is:

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

but it is actually:

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

So you are actually returning values from the 9th column, not the 6th.

The values in the 9th column are not percentages, so you have to divide by 100 (as previously suggested) if you want them to be.
So you could use this formula:

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

Would this forumal apply to all these other inflated cells? It worked for the one cell in question but it brings back the same value for all cells it's applied to.

 

[Joe4]

You need to make the same adjustments to those other formulas (it looks like they are not all the same).
Keep the one of the original VLOOKUPs (your original formula had it in there twice), but divide by 100 and add the IFERROR, i.e.
=IFERROR(VLOOKUP(...)/100,"NA")

Just follow that format and update the existing formulas.

 

[abombesposito]

You need to make the same adjustments to those other formulas (it looks like they are not all the same).
Keep the one of the original VLOOKUPs (your original formula had it in there twice), but divide by 100 and add the IFERROR, i.e.
=IFERROR(VLOOKUP(...)/100,"NA")

Just follow that format and update the existing formulas.

Got it, I am editing the formula and I see it's pointing to the correct cell ($A10,$A11,$A12, ect) but still returning duplicate response. What am I doing wrong

 

[Joe4]

Each of those entries you circled that match, if you look at the formula, do all the show "$A15" as the first argument of your VLOOKUP?
If they do, then they should all return the same value, as they are all exactly the same formula.
If you want them to return different values, then they need to look up different values, and you need to fix that in your formulas.

If the lookup values in the formulas indeed all are different, but the formulas are returning the same value, that suggests that you probably have your calculation set to manual mode, and you either need to manually kick-off a recalculation, or change your calculations to automatic mode.

 

[abombesposito]

Each of those entries you circled that match, if you look at the formula, do all the show "$A15" as the first argument of your VLOOKUP?
If they do, then they should all return the same value, as they are all exactly the same formula.
If you want them to return different values, then they need to look up different values, and you need to fix that in your formulas.

If the lookup values in the formulas indeed all are different, but the formulas are returning the same value, that suggests that you probably have your calculation set to manual mode, and you either need to manually kick-off a recalculation, or change your calculations to automatic mode.

Thanks for the back and forth on this. So each cell has different look up and it is set to automatic calculation. Maybe its my excel version? Are you able to download the excel and see if it does the same for you?

 

[Joe4]

I am getting the impression that this is not a form that you created yourself, but someone else did and you are just trying to maintain or change it.
I say that because it seems that you may not have a complete understanding of how this is all supposed to work, and how things are supposed to be related.

I went back and took a look at your document. Note that each section has three different rows, Gross, Net, and Benchmark. The formulas in these rows pull from different sheets. So you cannot copy the "Benchmark" formula to the "Gross" and "Net" rows, and expect it to show proper results (especially since the value listed in column A, which you are looking up, are often repeated). You can only copy the "Benchmark" formula to other "Benchmark" rows.

If the other rows formulas ("Gross" and "Net") need updating, you will want to update the original formula that was in there, using the format I should you in my earlier post.