Spreadsheet formula working partly ... not completly ...

[reglook0736]

I have a spreadsheet that works partly ... but not completely.

It works perfectly IF I have nothing in the "Adjustment (3rd Party) field. if there is nothing in there, everything works perfectly on all three sections. However, if I put a figure say, 3% in that field then the bottom one calculates it correctly and the top 2 are incorrect. I can't seem to figure out what I am doing wrong.

The 3 sections are for figuring the Bill Rate, Pay Rate and the Gross Profit. In all 3 sections the colored section is the one I am seeking to calculate with the white sections being the parts I would input.

Can you help me correctly figure the BR in the top, and the PR in the second when the Adjustment field has an amount in it.

I am having difficulty attaching the spreadsheet ... I have uploaded it but when I click the attachments button it does not show up ...

I will try to describe it below:


Calculating 3 items ... I always have all data except 1 item (I thought my algebra experience would come in handy ... but obviously I missed a day or 2)

I have a bill Rate, pay rate and gross profit ... but up top I also have a Burden that i need to subtract from it normally 17% and sometimes I have a 3rd party company in the middle that takes 3% from it as well.

So, in the one that works ALL THE TIME .... flawlessly (whether there is anything in the adjustment field or not)

this works when I have the BR and PR and I am seeking to calculate the GP%
I29*BURDEN)+I29))/H29),"0",(H29-(H29*Adj)-((I29*BURDEN)+I29))/H29)

where
H29 is the BR
I29 is the PR
J29 is what we are calculating the GP%
ADJ = 3rd party adjustment of 3%
BURDEN = 17%

If i input BR = 27.00 and PR = 15.50 then the GP% = 29.83% with the ADJ and Burden assigned as above. If I take the ADJ out (delete the entry) the GP% = 32.83% (3% less)

however in the upper sections where I am providing the

1.BR and GP% and solving for the PR
current formula entered:
=IF(ISERROR(-H19*(J19-1-Adj)/(BURDEN+1)),"0",(-H19*(J19-1-Adj)/(BURDEN+1)))

2. PR and GP% and solving for BR
=IF(ISERROR(-I13*(BURDEN+1)/(J13-1-Adj)),"0",(-I13*(BURDEN+1)/(J13-1-Adj)))

these 2 formulas work well if I have NOTHING in the ADJ field but do not produce the right result if I do have something entered in the ADJ field.

I hope this helps and I haven't confused you ... thank you so much for you help.

[reglook0736]

I am not sure how to delete the other one so I am posting the link to it here. I will mark them both solved so as not to waste people's time ...

http://www.mrexcel.com/forum/excel-q...ng-partly.html

[AliGW]

No need to mark it as solved - you won't get any help if you do!

Thank you for providing the link. That is all that we ask when you post elsewhere on the same topic.

[reglook0736]

it is solved ... thank you.

[AliGW]

Thanks for letting us know.