Hello there,
I am desperate for some help as all I can find are commission plans with this kind of logic.
1 $9.99
2 ? 5 $8.99 for each unit after 1st
6-15 $7.99 for each unit after 5th
16 ? 30 $6.99 for each unit after 15th
31 ? 100 $4.99 for each unit after 30th
101 ? 1000 $4.49 for each unit after 100th
I'd like to create and excel calculator with the above price rules.
Any help would be appreciated, i am at a loss. I tried to work with vlookup and never got to a result.
Thanks
For an amount if B2, use
=MIN(1,B2)*9.99+MAX(0,MIN(4,B2-1))*8.99+MAX(0,MIN(10,B2-5))*7.99+MAX(0,MIN(15,B2-15))*6.99+MAX(0,MIN(70,B2-30))*4.99+MAX(0,B2-100)*4.49
Bernie Deitrick
Excel MVP 2000-2010
Try
=SUMPRODUCT(($E$2>=$B$2:$B$7)*($E$2-$B$2:$B$7),$C$2:$C$7)
See attached: table in B2:C7
see for explanation: http://www.mcgimpsey.com/excel/variablerate.html
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks