+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Different calculation depending on cell entry

  1. 03-13-2010, 10:42 PM #1
    Antonia
    Antonia is offline
    Registered User
    Join Date
    03-13-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Different calculation depending on cell entry

    Hi.

    I am having a lot of trouble building what is probably a very simple formula in Excel 2007.

    I would like for my desired cell to do the following calculations depending on the number in, let's say, cell B16:

    If the number in B16 is 1, 2, or 3, the desired cell should multiply the number in B16 by $7500 and display the result. If B16 is 4,5,6, or 7, the desired cell should multiply the number in B16 by $5000 and display the result. If B16 is 8 to infinity, the desired cell should multiply the number by $4000 and display the result. If the number in B16 is 0, then the result in the desired cell should be $0.

    If someone could help me understand the correct entries to make the above happen (and end my pain), I would be forever grateful.

    Thank you!
    Last edited by Antonia; 03-14-2010 at 04:23 AM.
    Reply With QuoteRegister To Reply
  2. 03-13-2010, 10:49 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Different calculation depending on cell entry

    Try

    =B16*Lookup(B16,{0,4,8},{0,7500,5000,4000})
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-14-2010, 03:16 AM #3
    Antonia
    Antonia is offline
    Registered User
    Join Date
    03-13-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Different calculation depending on cell entry

    Hi NBVC,

    Thank you very much for the quickly response. Very sadly, the entry provided didn't work

    The cell didn't calculate anything for values 1,2,3 (the cell result was just zero). Then for all the other values (4,5,6,7,8,9...), the result was incorrect. (Example, 4x$5000 is $20000, but the result that was generated was $30000.)

    Help please?

    Antonia
    Reply With QuoteRegister To Reply
  4. 03-14-2010, 03:22 AM #4
    JeanRage
    JeanRage is offline
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Different calculation depending on cell entry

    Hi Antonia,

    Would you mind posting a sample worksheet ...
    for a tailor made answer ...
    Reply With QuoteRegister To Reply
  5. 03-14-2010, 03:41 AM #5
    JeanRage
    JeanRage is offline
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Different calculation depending on cell entry

    Could you try
    =B16*LOOKUP(B16,{0,1,4,8},{0,7500,5000,4000})
    HTH
    Reply With QuoteRegister To Reply
  6. 03-14-2010, 03:58 AM #6
    Antonia
    Antonia is offline
    Registered User
    Join Date
    03-13-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Different calculation depending on cell entry

    Hi JeanRage,

    Thanks for your response. I will try your suggestion as well. It looks like it's similar to the message I received above except that I should add 1. I will give it a try. In response to your previous message, I am also attaching this worksheet that may explain better what I am looking for--just in case. I don't have a sample worksheet per se, since I am working from scratch.

    Thank you SO much for your help!

    Antonia
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-14-2010, 04:16 AM #7
    JeanRage
    JeanRage is offline
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Different calculation depending on cell entry

    Hi Antonia,

    Much clearer now ...
    see attached

    HTH
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-14-2010, 04:23 AM #8
    Antonia
    Antonia is offline
    Registered User
    Join Date
    03-13-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Different calculation depending on cell entry

    Dear HTH /JeanRage,

    You are SO the best in the West! Thank you so much-It works perfectly! You've saved me hours of pain and bitter tears. Much obliged. I'll close out this post now. Thank you again so much for graciously sharing your expertise.

    Antonia
    Reply With QuoteRegister To Reply
  9. 03-14-2010, 12:20 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Different calculation depending on cell entry

    Mine should have been:

    =B16*Lookup(B16,{0,1,4,8},{0,7500,5000,4000})
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1