Formula that will change an amount based on a cell meeting certain thresholds

[arkadd61]

I have a spreadsheet that I'm working on and I need help with a formula in Cell M7. I have attached a copy of the spreadsheet for your viewing. In this Cell, I need it to automatically change based when Cell when Cell K4 meets certain thresholds. For example if K4 reaches $600, then M7 should reflect $600x1=$600 + O4 + Q4. If it reach $800 M7 should reflect $800x2=$1600 + O4 + Q4 and so on. I have broken down below what I would like M7 to reflect for each threshold.

I have inputed the formula into the example spreadsheet I was trying to work with and it might give you an idea of what I'm trying to acomplish. Any help would be greatly appreciated.

What I want Cell M7 to reflect as the amounts meet certain thresholds:
(K4$600)x1=$600+O4+Q4
(K4$800)x2=$1600+O4+Q4
(K4$1000)x3.5=$3500+O4+Q4
(K4$1200)x4=$4800+O4+Q4
(K4$1400)x4.5=$6300+O4+Q4
(K4$1600)x5=$8000+O4+Q4
(K4$1800)x5.25=$9450+O4+Q4
(K4$2000)x5.5=$11000+O4+Q4
(K4$2200)x3.75=$8250+O4+Q4
(K4$2400)x4=9600+O4+Q4

Thank you ahead of time for any help!
Semisi

[bebo021999]

What do you mean "reach $600"?
How about $500?

[Pete_UK]

I would suggest that you set up a little 2-column table somewhere like this:

0 none
600 1
800 2
1000 3.5
1200 4
1400 4.5
1600 5
1800 5.25
2000 5.5
2200 3.75
2400 4

Suppose you have this in cells V4 to W14. Then you could have this simpler formula in M7:

=IFERROR(K4*VLOOKUP(K4,V4:W14,2)+O4+Q4,"You're fired")

It is important to have some text in cell W4, as this is what triggers the error condition.

Hope this helps.

Pete

[sktneer]

Your formula will be like this........

Please Login or Register  to view this content.

I hope it helps.

[bebo021999]

Or try this with LOOKUP:

Please Login or Register  to view this content.

[arkadd61]

Wow! Thank you for the help. I entered your formula into the cell, but the only thing I'm wondering if could be tweaked is, in between the different threshold amount it there a way to not contnue to sum up totals in between the different threshold amounts? Meaning the amount does not adjust for values in between the various thresholds in the formula you created.

[arkadd61]

You formula works just fine. The only change I need is that the formula does NOT continue to sum up totals in between the various thresholds. Mean the next amount in not calculated until the next threshold is reached. Please let me know if you need further clarification.

[FDibbins]

I see at least 3 different formulas suggested above. Which 1 did you use?

[arkadd61]

I used bebo210999 and I also tried sktneer's formula. Both work perfectly, except for one little tweak I need if possible to do.

[arlu1201]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

[arkadd61]

I need to make a slight adjustment if its possible to do with a formula. The formula's that bebo210999 and sktneer provided is doing most of what I need the formula to do. The only tweak I need is I only want the formula to calculate the multiplier when the threshold has been met. See my example below.

Whe the agent reachs $600 dollars in sales, then he receives the commission of $600 + O4 + Q4 and that's it. Any additional commissions generated into Cell M7 from $600 to the next threshold of $800 I do not want the formula to accumulate, I only want it to calculate when the next threshold has been met. However I still want Cells O4+Q4 to continue to accumulate.

$1 - $599 No Calculation
(K4$600)x1=$600+O4+Q4
-
$601 - $799 No calculation
-
(K4$800)x2=$1600+O4+Q4

$801 - $999 No Calculation

(K4$1000)x3.5=$3500+O4+Q4

[sktneer]

Replace all logical tests for K4 i.e. K4>2400, K4>2200,K4>2000.........K4>600 with K4=2400, K4=2200,K4=2000.......K4=600 in the formula and see if it does what you want.

[arkadd61]

I'll give that a shot. Thank you!