+ Reply to Thread
Results 1 to 17 of 17

Same Formula, but getting different results

  1. #1
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    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.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    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.

    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.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    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
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    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.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #7
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    Re: Same Formula, but getting different results

    Thank you FDibbins, regret I am still not able to make it work
    Please see attached file.

    May be use index/match function?
    Attached Files Attached Files

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    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. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

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

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    Re: Same Formula, but getting different results

    I was overthinking it - use Rory's suggestion.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Same Formula, but getting different results

    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

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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.
    Dave

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Same Formula, but getting different results

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

  14. #14
    Forum Contributor mikehk's Avatar
    Join Date
    09-05-2017
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    483

    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. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    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. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Same Formula, but getting different results

    Glad we could help, and thanks for the rep.

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Same Formula, but getting different results

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. using results of a formula in a count if when the results are a date
    By moneypennie21 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-04-2015, 10:29 AM
  2. [SOLVED] Using VLookup but results show formula instead of results...
    By excel me in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-28-2013, 09:40 PM
  3. Replies: 3
    Last Post: 06-19-2013, 03:40 AM
  4. Replies: 14
    Last Post: 06-27-2012, 04:26 PM
  5. Replies: 11
    Last Post: 04-14-2011, 05:07 PM
  6. Replies: 3
    Last Post: 03-31-2010, 11:47 AM
  7. Copy Formula results and paste only results
    By Tom K in forum Excel General
    Replies: 1
    Last Post: 01-10-2008, 12:23 PM

Bookmarks

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