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.
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.
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?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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.
Does that answer your question?
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.
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
Originally Posted by shg
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.
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
Thank you FDibbins, regret I am still not able to make it work
Please see attached file.
May be use index/match function?
You could use:
=(B15*(1-VLOOKUP($D$13,$N$2:$O$11,2,FALSE)%))*(1-VLOOKUP($D15,$K$2:$L$11,2,FALSE)%)
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)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I was overthinking it - use Rory's suggestion.
Adjusting my suggestion to use vlookup, instead...
=(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
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.
Dave
Dave, yes, that is what I got too, so I decided to go with vlookup instead
Thank you so much everyone...I got this working finally. Highly appreciate your help.
Rorya's formula works well...thank you Rorya
You're welcome!
This is what I found, too, and why I used VLOOKUP.It returns Airbnb and hence 0.08 not Expedia (0.1) as intended.
Glad we could help, and thanks for the rep.
You're welcome. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks