Different calculation depending on cell entry

**Antonia** · 03-13-2010, 10:42 PM

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!

**NBVC** · 03-13-2010, 10:49 PM

Try

=B16*Lookup(B16,{0,4,8},{0,7500,5000,4000})

**Antonia** · 03-14-2010, 04:16 AM

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

**JeanRage** · 03-14-2010, 04:22 AM

Hi Antonia,

Would you mind posting a sample worksheet ...
for a tailor made answer ...

**JeanRage** · 03-14-2010, 04:41 AM

Could you try

=B16*LOOKUP(B16,{0,1,4,8},{0,7500,5000,4000})

HTH

**Antonia** · 03-14-2010, 04:58 AM

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

**JeanRage** · 03-14-2010, 05:16 AM

Hi Antonia,

Much clearer now ...
see attached

HTH

**Antonia** · 03-14-2010, 05:23 AM

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