Alternate pricing formula

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have an "IF" formula that I use for pricing. How can I
use a logic formula with more than 7 segments? The "IF"
formula max's out at 7 segments, I would like to have
about 13 segments with different multipliers.
Ex: IF(A1<5,A1*5,IF(A1<10,A1*4.....
 
This is better implemented as a lookup table. For instance, say on sheet
2 you have:

A B
1 Quantity Multiplier
2 0 5
3 5 4
4 10 3
....


Then back in Sheet1, your pricing formula could be:

=VLOOKUP(A1,Sheet2!A:B,2,TRUE)*A1
 
Jeff

one way:

=A1*VLOOKUP(A1,{0,5;5,4;10,3;15,2;20,1},2,TRUE)

The "break points" are 0, 5, 10, 15 and 20 but you can extend to what you
like.

0 to 4 (that is, <5) will multiply by 5; 5 to 9 (<10) will multiply by 4; 10
to 14 will multiply by 3; etc

Or just have a look at the Help for VLOOKUP

Regards

Trevor
 
Back
Top