Spreadsheet working ... Partly

[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.

 

[reglook0736]

I am having difficulty attaching the spreadsheet to the post ... not sure how I screwed it up ...

 

[AliGW]

Welcome to the forum!

Unfortunately, attachments are not allowed, so could you please provide us with some sample data and tell us the formulae that you are using that sometimes work and sometimes don't?

 

[reglook0736]

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 don't know how to delete it ...

the link to the other one is right here:

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

 

[AliGW]

You don't need to delete it. All you have to do when you cross post is provide links.

 

[reglook0736]

it is solved, thank you.