Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to write an IF formula for an order form where a discount is applied depending on the amount of units ordered.

The discounts are:

  • 0 to 24 units - 0 discount
  • 24 to 59 units – 5% discount
  • 60 to 95 units – 10% discount
  • 96 to 131 units – 15% discount
  • 132 to 263 units – 20% discount
  • 264+ units - 30% discount

The cell that I am taking the total from is J1028. I have no idea how to do IF formulas so desperately need some help.

share|improve this question
1  
Why is there an umlaut over the H in this question? –  Anderson Green Oct 2 '12 at 2:08
    
@AndersonGreen I've suggested it be edited to plain I since it seems the umlaut may be affecting the ability to find 'IF' when searching. –  pnuts Dec 21 '12 at 18:23

4 Answers 4

Similar to @chrisneilsen's response, you could try using a VLOOKUP formula with the range_lookup parameter set to True:

=VLOOKUP(D2,$A$2:$B$7,2,TRUE)

This requires that the lookup table be sorted in ascending order by Units, with each Unit value representing the lower bound of the discount range:

enter image description here

share|improve this answer
    
Neat, but perhaps a little risky in inexpert hands, at least without mention that TRUE here means "an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned". –  pnuts Oct 19 '12 at 20:25

For flexibility (so discounts and breakpoints can be changed later without necessarily needing to revise the formula) I’d suggest a table as in L1:M6 in the example below. Then apply the IF formula as shown, without the need for sorting or adding a limit value.

SU482183

The IF construction is (test,pass,fail) and when nested the sequence is left to right.

[Assumes 5% discount for 24 units]

share|improve this answer
1  
I think @chrisneilsen and I were both aiming more for flexibility - using INDEX/MATCH or VLOOKUP, if the OP needed to completely rework their bands and add/remove multiple criteria, they would only need to add the adjustments and resize the range in the formula. With an IF (namely a nested one), you would have to manually account for each change separately. Plus, pre-Excel 2007, the nested IFs limit is 7. –  RocketDonkey Oct 19 '12 at 20:36

This tutorial will walk you through what you'll need to know about IF statements in Excel 2007.

Have a look at the Complex If Functions part; it should point you in the right direction.

share|improve this answer

Actually IF is not the best way to do this. Use Index Match instead.

Setup your discount table as shown below.
The reverse order of quant is needed for Match to work.
The large number 1E10 is an arbitary number, larger than any order you will receive.

=INDEX($B$2:$B$7,MATCH(J1028,$A$2:$A$7,-1))

Demo

share|improve this answer
    
Small point (row 1000+ is not helpful in an example!) but your formulae are inconsistent for what is to be matched. IF is generally better understood than INDEX and MATCH (and is mentioned in OP) so I'd be interested in why not the best way - performance? –  pnuts Oct 19 '12 at 20:18
1  
@pnuts IF is generally better understood than : thats a matter of opinion. IMO Index Match (or VLookup for that matter) is basic Excel, and is easier to understand and debug than multiple nested IF's –  chris neilsen Oct 19 '12 at 21:49
    
Thank you (and @RocketDonkey) for the clarification. I tried googling “EXCEL ={function} function” for each of IF, LOOKUP, MATCH and INDEX and came up with about the same number of hits for IF as for the other three combined. Might indeed indicate IF is more exotic?! I fully agree complicated IF statements are difficult and take your point that adjusting the range would be a bit easier, though (in my opinion!) it is roughly “7 of one or half a dozen of the other” where the nests are simple and so similar. My excuse is the question title. –  pnuts Oct 19 '12 at 22:31
1  
@pnuts or 6 of one :) –  RocketDonkey Oct 19 '12 at 23:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.