Need to change hourly rate + overtime rate if total pay for period goes over $1,555.95

The problem: I have to change the hourly rate + overtime rate if total pay for period goes over $1,555.95. The commissions are supposed to offset the pay and the hourly and overtime both need to decrease to a number to offset any amount of commission received.


What I tried: I tried using an IF Formula stating: AND(F9>55.95,F13>1555.95) for the Logical_test, then doing 12.50 for false. The problem I've run into is I have absolutely no clue on how to decrease a number to an amount based on keeping the value of 1,555.95. In other words, I have no idea what to put into the true section. If anyone has any ideas on how to do this, please tell me!

Thank you!!!

Question: what do you mean by, "the hourly and overtime both need to decrease to a number to offset any amount of commission received?"

Reduced to what? Reduced so that the Total pay for the period does not exceed $1,000 unless commissions exceed $1,000?

BTW: you could simplify some of your formulas. Cell C7 =MIN(C6,80) and Cell C8 =MAX(C6-80,0) and cell C10 =C9*1.5.

What if total pay is lower 1,555,95? Leave Rate as itself or New Rate?

Below formula populates new rate that aims to total pay = 1,555.95 disregards to hours:

=(1555.95-F9)/(C7+C8*1.5)

The Total pay links to Rate
If Total pay exceed a value, change Rate.
That is a circular reference, excel could not do that.
I suggest use other cell for "NewRate" and put my formula in #4

Oh wow, I just used your previous formula in my Hourly Pay Rate and it worked perfectly!

The only problem is if there is a way to not increase the Hourly Pay rate above 12.50 for Regular and the 18.50 for Overtime Hourly Pay Rate.
I only want it to decrease if the Total Pay for Period reaches 1,555.95. I don't want it to increase over 12.50 and 18.50.
The formula makes it so it will always equal 1,555.95 because it can increase the payrate and overtime to an insane amount to compensate for a lower commission.

If you can do this, you're amazing!

I was locked out of the site for while. I got the same answer, but showing my work. I am using Max and Min in my formula to keep the rate from going below zero or above 12.50.

=MIN(MAX(IF((C7-1.5*C8)<>0,(F15-F9)/(C7-1.5*C8),0),0),12.5)

I was locked out of the site for while. I got the same answer, but showing my work. I am using Max and Min in my formula to keep the rate from going below zero or above 12.50.

=MIN(MAX(IF((C7-1.5*C8)<>0,(F15-F9)/(C7-1.5*C8),0),0),12.5)

There is an add in called solver File -> Options - Add Ins -> Excel Addins. Once you have it enabled, it will appear on the far right of the data ribbon.

Set it up like the picture and click on the solve button on the bottom.