bill rate / pay rate calcs

[reglook0736]

I have attached a spreadsheet ... there are 3 sets of data in it

the first one works perfectly and has a formula for calculating the gross profit % given 4 variables: burden, 3rd party adjustment, bill rate and pay rate. I am looking for 2 formulas: 1 that calculates the bill rate given the other 4 variable are known and 1 that calculates the pay rate given the other 4 variables.

I can't seem to get it right.

thank you for your help.

[shg]

You were supposed to pay attention in that algebra class ...

[Crooza]

In H7

=-I7*(BURDEN+1)/(J7-1-Adj)

and in N7

=-M7*(O7-1-Adj)/(BURDEN+1)

[reglook0736]

you guys whipped that up in just a couple of mins ... I do fancy myself as 'decent' in algebra ... because of that ... I spent 2 days of my life (never to get them back) trying to solve for these two ... I couldn't get around always finding myself with the variable I was solving for on both sides of my equation ... I just couldn't figure it out ... I am VERY appreciative.

2 things:
1. did you notice a simpler version of my long equation?
2. any pointers on HOW you came up with it ... where was I going wrong? I would love to learn a little here not just take the answer ...

thank you again!!!

[shg]

Just simplified the expression and removed the unnecessary parens.

[peterrc]

Hi,

I couldn't help but notice that if the "Burden" is set at 17% all tables match.
All 3 tables refer to "Burden".
But if you change the "Burden" to any other percentage they don't match.
Is this supposed to happen?
Or am I missing something?

Regards

peterrc

[Crooza]

Not on computer at present(iPad only) but I recall your formula had the form
If(iserror(formula),0,formula)

You can simplify this by

Iferror(formula, 0)

The other issue is you had a lot of redundant brackets. You don't need brackets around things that follow the normal order of operations. So ((A1*B1)+C1) can be written without brackets but (A1+b1)/c1 needs the brackets to get the correct order of operation

[Crooza]

Hi,

I couldn't help but notice that if the "Burden" is set at 17% all tables match.
All 3 tables refer to "Burden".
But if you change the "Burden" to any other percentage they don't match.
Is this supposed to happen?
Or am I missing something?

Regards

peterrc

If you change just the burden figure then you're right the three tables won't match. The first table results for GP will need to be put into the second and third table otherwise you're not using the same variables