# Solved: Excel Discount Formula



## dadufresne (Nov 1, 2008)

Hello: I'm getting totally frustrated trying to come up with the formula to calculate a discount in Excel 2003. A 5% discount is offered for between 21 and 30 items and a 10% discount is offered for between 31 and 50 items. Can someone help me? It would be greatly appreciated! Trust me.....frustration level about to volcano at this point!


----------



## OBP (Mar 8, 2005)

Do you have the quantity (21-30 & 31-50) in a Cell?


----------



## dadufresne (Nov 1, 2008)

I do have the total quantity summed to one cell. Thank you for not giving up!


----------



## dadufresne (Nov 1, 2008)

I'm having problems with my homework. Is there anyone out there who can offer me a solution to this multiple discount issue? I've tried the following and can't for the life of me figure out where I'm going wrong:

IF (C11<20, =30, 5)

IF (C11<20=30, 10)

I need to calculate a 5% discount if the quantity equals 20 - 30 pieces and a 10% discount if the quantity equals 31 - 50 pieces. I have this calculating in two separate fields, but it doesn't have to be that way if someone can tell me how to do it all in one field.

C11 is the quantity sum field.

I'm starting to lose faith in these forums. This is the second time in about a month that I've had an immediate response and then the help stops. Please, can someone help me?


----------



## bomb #21 (Jul 1, 2005)

Sorry, not if it's homework.


----------



## dadufresne (Nov 1, 2008)

It's not an Excel class, it's an MIS class. And I'm not 18, I'm 48. What's the problem?


----------



## Zack Barresse (Jul 25, 2004)

_Posted via mobile device.

Take a look at the LOOKUP() function. set up a table of values and what they correlate to.

HTH_


----------



## bomb #21 (Jul 1, 2005)

dadufresne said:


> It's not an Excel class, it's an MIS class. And I'm not 18, I'm 48. What's the problem?


"Problem": although it's not an actual rule, _some_ (self included) have ... "reservations" about helping with *school* homework. While you _may_ be 48, *we're* not psychic. 

Nested IF Statements


----------



## OBP (Mar 8, 2005)

dadufresne, I apologise for not getting back to you last night, but after over 10 hours on the computer and a lot of it on the Forums my Wife decided enough was enough and kicked me off so she could go on ebay and QVC.
We are not paid to "work" on here, we are volunteers, so a little patience would be appreciated
What I was going to post is attached, I know it doesn't go along with the Excel Gurus Lookups, but it is simple to use.


----------



## dadufresne (Nov 1, 2008)

I do understand about having a life and I'm sorry if I sounded frustrated. I'd spent all day doing homework and this formula thing was wearing on me. As a former techie, I totally understand. Not being an Excel person, I just wanted some help and got a little frustrated. Please accept my apologies.

While the IF statement link from bomb21 was very helpful and the percentage spreadsheet from OBP was also helpful, there is still something wrong with my statement and I'll be darned if I can see it.

My quantity is 30 (C11) so a 5% discount should be calculating, but it's not. The 10% discount calculates perfectly. Any ideas where I'm going wrong? Can't I have either discount appear in the same field depending on the quantity?

=IF(AND(C11>20,C11=30),E12*0.05,IF(AND(C11>30,C11<51),E12*0.1))

Any insight would be very much appreciated!


----------



## dadufresne (Nov 1, 2008)

I finally got it!

=IF(AND(C11>=20,C11<=30),E12*0.05,IF(AND(C11>30,C11<51),E12*0.1))

Thanks for your help!


----------



## OBP (Mar 8, 2005)

C11=30)
<31 ?


----------



## Zack Barresse (Jul 25, 2004)

Will that list ever grow? Or how about need to be changed? If you have a "yes" or "maybe" answer for either of those, I'd highly recommend NOT using IF() functions, but a LOOKUP(). You should be using an IF() statement for the low and high ends, that's it. Your formula above isn't too bad, but anymore will be overtaxing IMHO. And you don't take into account the second IF clause false statement.

This should also work...

=IF(C11<20,1E12,IF(C11<=30,E12*0.05,IF(C11<51,E12*0.1,_LastFalseStatementHere_)))


----------

