Max wrote...
In Sheet1 (Set up a reference table)
-----------
List in A2:A5 : 0, 300.01, 500.01, 1000.01
List in B2:B5 : 25%, 20%, 15%, 10%
Put in:
C3: =B2*(A3-0.01)
C4: =B3*(A4-A3)+C3
C5: =B4*(A5-A4)+C4
In Sheet2
----------
Assume your sales are listed in E22 down
Put in F22:
=VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE)))
*VLOOKUP(E22,Sheet1!$A$2:$C$5,2,TRUE)
....
No need to include the 4th argument to VLOOKUP when it's TRUE. What's
gained?
This could be done with a single formula without ancillary cells.
=SUMPRODUCT(IF(C6{300;500;1000;""},{300;500;1000; ""},C6)-{0;300;500;1000},
IF(C6{0;300;500;1000},{0.25;0.2;0.15;0.1}))
|