# Same Formula, but getting different results

1. ## Same Formula, but getting different results

I am unable to figure out what I get different results from
the same formula ...... don't know what I am missing here,
tried format cells etc...but won't get it working.

Please see the attached sample file.

Thank you.

2. ## Re: Same Formula, but getting different results

To start with, all formatting does, is change the appearance of a cell's contents, not the underlying contents

Next, Im not sure I see what your problem is?
If you are referring to the difference between Test1 A1 and test2 A1, they are referencing different cells.
K21=B15*(1-L2)*(1-O2)
K29=B15*(1-L3)*(1-O3)

If that was not the problem, please explain what is?

3. ## Re: Same Formula, but getting different results

It would really help if you identified where the "same formulas" are; identify the cell names.

I see two sections labeled "Test 1" and "Test 2". Both say "correct result should be" followed by tables with A1, A2 etc on the left.

If we assume that the description "correct result should be" is incorrect, and your concern is that the value for A1 is different in the two tables, the reason is: the formulas are not the same, as is readily apparent by careful inspection.

For Test 1, the formula for "A1" in cell K21 is: =B15*(1-L2)*(1-O2)

For Test 2, the formula for "A1" in cell K29 is: =B15*(1-L3)*(1-O3)

Note the differences in the referenced cells: L2 vs L3, and O2 v2 O3.

Do you really have a very different question?

I wonder if the "Test 1" and "Test 2" tables are just attempts to debug the LOOKUP formula in E15, for example.

4. ## Re: Same Formula, but getting different results

If I assume that your "correct result should be" is correct, it looks like it is a simple case of parentheses out of place. You can follow along using the Evaluate formula tool, if needed https://support.office.com/en-us/art...6-a70aa409b8a7

Using the order of operations on the current formula for the A1 test1 case:

A) lookup the rate for the A1 disccode (0.05) and subtract from 1 (0.95)
B) lookup test1 fixed fee value (0.1) and multiply by result of A (0.095) [If I understand, this is what is written but not what you want]. subtract from 1 (0.905)
C) Multiply by value in B15 (1000) to get 905.

So the calculation looks like 1000*(1-0.1*(0.95))=905.

I think you need to move one of those closing parentheses up after the first LOOKUP() to get 1000*(0.9)*(0.95)=855

5. ## Re: Same Formula, but getting different results

Thank you everyone for trying to help me and sorry if I didn't explain well,
my english is not good..so please excuse me.

I attach herewith sample file again with more explanation. Hope this helps.

Thank you.

6. ## Re: Same Formula, but getting different results

As MrShorty pointed out you had missing ()
=(B15*(1-LOOKUP(\$D\$13,\$N\$2:\$N\$11,\$O\$2:\$O\$11))*(1-LOOKUP(\$D15,\$K\$2:\$K\$11,\$L\$2:\$L\$11)))

You need to calc 1 part 1st, then apply the next % to THAT answer

7. ## Re: Same Formula, but getting different results

Thank you FDibbins, regret I am still not able to make it work

May be use index/match function?

8. ## Re: Same Formula, but getting different results

You could use:

=(B15*(1-VLOOKUP(\$D\$13,\$N\$2:\$O\$11,2,FALSE)%))*(1-VLOOKUP(\$D15,\$K\$2:\$L\$11,2,FALSE)%)

9. ## Re: Same Formula, but getting different results

Try this:

=(B15-B15*(VLOOKUP(\$D\$15,\$K\$2:\$L\$11,2,1)/100))-(B15-B15*(VLOOKUP(\$D\$15,\$K\$2:\$L\$11,2,1)/100))*(VLOOKUP(\$D\$13,\$N\$2:\$O\$11,2,0)/100)

10. ## Re: Same Formula, but getting different results

I was overthinking it - use Rory's suggestion.

11. ## Re: Same Formula, but getting different results

=(B15*(1-VLOOKUP(\$D\$13,\$N\$2:\$O\$11,2,0)%)*(1-VLOOKUP(\$D15,\$K\$2:\$L\$11,2,0)%))
(pretty much the same as Rory)

Also note that in your 1st file, you shows %, but the 2nd 1 just had whoel numbers

12. ## Re: Same Formula, but getting different results

As I understand it your formula wasn't working because LOOKUP is approximate match only and data needs to be sorted for it to work as you apparently expect it to.

If you enter just this portion of your formula in an empty cell

=LOOKUP(\$D\$13,\$N\$2:\$N\$11)

It returns Airbnb and hence 0.08 not Expedia (0.1) as intended.

I concur. Use VLOOKUP.

13. ## Re: Same Formula, but getting different results

Dave, yes, that is what I got too, so I decided to go with vlookup instead

14. ## Re: Same Formula, but getting different results

Thank you so much everyone...I got this working finally. Highly appreciate your help.
Rorya's formula works well...thank you Rorya

15. ## Re: Same Formula, but getting different results

You're welcome!

It returns Airbnb and hence 0.08 not Expedia (0.1) as intended.
This is what I found, too, and why I used VLOOKUP.

16. ## Re: Same Formula, but getting different results

Glad we could help, and thanks for the rep.

17. ## Re: Same Formula, but getting different results

You're welcome. Thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1