I'm having trouble coming up with a dragable code for this project.
Anything 100 or under is simple, I just used =IFS(H6<E7,H6*F6)
But above 100, I'm not sure what to do. If I hand coded it at 600, it would be (100 *2 400 *4 100 *6).
I'm drawing a blank on what I should do, any help would be appreciated.
CodePudding user response：
Very interesting lookup problem here! I've given it a few thoughts and came up with something that should help.
I've laid out the brackets horizontally, like this:
0 100 500 1000
40
101
1500
6000
13000
600
I have the inputs in column H, column I is left blank, and the brackets begin in column J.
So first we lookup the unit price per bucket. Easy enough, a plain old VLOOKUP will do. So J6 has this:
=VLOOKUP(J$5,$D$6:$F$9,3,TRUE)
Underneath this, in row 14, I've made a copy of this range, this time to calculate the number of units that belong under each bucket. This one was fun to come up with and could probably be simplified, but here's what I have in J14:
=MAX(0,MIN(MAX(0,$H14-J$5),VLOOKUP(J$5,$D$6:$E$9,2,FALSE)-SUM($I14:I14)))
Then I made another copy underneath to just multiply the two tables, so I have this in J22:
=J14*J6
By making the sum of the amounts under each bracket, we get what we're looking for:
- 40 => 80$
- 101 => 204$
- 1500 => 9800$
- 6000 => 54800$
- 13000 => 124800$
- 600 => 2400$
So, with a little bit of clean-up the helper ranges can certainly be removed, but then it becomes a monster formula that's pretty hard to tweak and/or debug later - whether the best solution is to do that or keep the helper ranges (and move or hide them, perhaps), is up to you!
Slight little edit, for the lookups to work you either need one more top bracket, or to have something like 9999999 as the upper bound for the "1000 " bracket.