3 Caluclations From Similar Formula - Help Needed

[Celtic_Avenger]

Please Help


I have a simple page in a spredsheet to calculate margins verses pa
and bill rates.

One cell must be completed with a costings percentage to add into th
calculations. The number is in a percentage format, 0.1037 = 10.37%
and is in Cell (A1)
I shall call this Costs for the purpose of the calculations.

3 cells can also be entered into the sheet. These are Pay Rate (B1)
Bill Rate (C1), and Margin (D1).

If you enter the Pay Rate and the Bill Rate, another cell calculate
the Margin
If you enter the Pay Rate and the Margin, another cell calculates th
Bill Rate
If you enter the Bill Rate and the Margin, another cell calculates th
Pay Rate

The calculations appear in cells lower on the page.
Calculated Pay Rate = (B2), Calculated Bill Rate = (C2), Calculate
Margin = (D2)

I have the formular for the first two options, but I am havin
difficulty working out the third.



The Formular to calculate the Margin =

Calculated Margin = 1-(((pay rate * costs) + pay rate) / bill Rate)

or

D2=1-(((B1*A1)+B1)/C1


The Formular to calculte the Bill Rate =

Calculated Bill Rate = ((pay rate * costs) + pay rate) / (1-margin)

or

C2=((B1*A1)+B1)/(1-D1)


Could someone help me with the formular to calculate Pay Rate, when th
Bill Rate and Margin are entered.


Thanks In Advance.


Celtic_Avenger
:confused

 

[kkknie]

Algebraicly (w/ M=Margin, P=Pay Rate, B=Bill Rate):

M = 1-(((P * C) + P) / B)

1 - M = ((P * C) + P) / B

(1 - M) * B = (P * C) + P

(1 - M) * B = P * (C + 1)

P = ((1 - M) * B) / (C + 1)

Checking math based on second formula...

B = ((P * C) + P) / (1 - M)

B * (1 - M) = (P * C) + P

B * (1 - M) = P * (C + 1)

P = (B * (1 - M)) / (C + 1)

So, your formula would be:

Pay Rate = (Bill Rate * (1 - Margin)) / (Cost Rate + 1)

B1 = (C1 * (1 - D1)) / (A1 + 1)