IF Formula Help

[FrustratedInWV]

I'm working on a spreadsheet. What I need to do is show a profit or loss - if we pay someone x amount of money up front, then we get the actual amount at a later date, and the amount of the actual is 0, then I need to show a credit of the money paid out which will be credited toward future transactions. The numbers in both columns will be variable. For example, if Column A is 152.00 and column B is 0 then how do I get it to show -152.00 without having to change it every time. I hope this is not confusing.

 

[kweaver]

Welcome.

Should it look like this?

Book1
	A	B	C
1	Paid	Actual	Future
2	$152.00	$0.00	-$152.00
3	$200.00	$100.00	-$100.00
4	$200.00	$200.00	$0.00
5	$300.00	$400.00	$100.00
Sheet3

Cell Formulas
Range	Formula
C2:C5	C2	=B2-A2

 

[FrustratedInWV]

I'm still not getting the correct answer. We pay out a preliminary commission when a salesman places the order. It's a portion of the entire job. Upon completion, we do the job audit and then make whatever adjustment is necessary to the final commission. Sometimes the job tanks and the commission is lost. Example... the job is bid/ordered at a commission of 100.00. At the end it should be approximately 100.00 so they would get the remainder of the 100.00 commission due on that job. But there were unexpected costs either in equipment, technical services, etc. that cause the job to lose money and then there is no commission on the job if those costs or expenditures were more than was budgeted. So we need to reflect the loss as 100.00 - the zero commission which should reflect a "take back commission" off 100.00 or -100.00 which is factored into the commission calculations for all jobs for a month so that number needs to be a negative number. That is the disconnect. It's giving me a positive number instead of the negative number. In all instances it works except when the result of the audit is 0 and all commission is lost. I need a formula that provides the correct adjustment whether it is a loss of the entire commission, part of the commission or additional commission is owed. The final number is the amount to be adjusted to their original commission. Hopefully this helps explain what I'm trying to do.

 

[kweaver]

A few examples would help.

 

[FrustratedInWV]

This is a rough idea of what I'm trying to do (what we want to show)

 

[kweaver]

A few more examples would clarify the fog for me. What happens with values in "final audit" ?
What if estimated is larger than when ordered or visa versa?
etc., etc., etc.

 

[FrustratedInWV]

The final audit determines whether additional money is earned on a job or lost / credited on a job. So they might lose 25.00 or gain 25.00 depending on whether the job comes in at 75.00 or 125.00. Either way the formula gives me the same answer. I need a formula that I don't have to tweak every job I audit. I am fairly good at Excel but haven't had so much training in Excel with formulas. I know there's a way to do it but can't seem to find the one I need. The first line shows that we should owe the sales rep money, but the second line SHOULD show that he owes us and should be -25.00. Does that help? See if this helps.

 

[FrustratedInWV]

The final audit determines whether additional money is earned on a job or lost / credited on a job. So they might lose 25.00 or gain 25.00 depending on whether the job comes in at 75.00 or 125.00. Either way the formula gives me the same answer. I need a formula that I don't have to tweak every job I audit. I am fairly good at Excel but haven't had so much training in Excel with formulas. I know there's a way to do it but can't seem to find the one I need. The first line shows that we should owe the sales rep money, but the second line SHOULD show that he owes us and should be -25.00. Does that help? See if this helps.

View attachment 4220

the when ordered is more information - the main column we really need is the difference column so that correct commissions can be paid out. They just like to see how the job progressed but the final number is the one that is important. Sometimes costs go up between when it is quoted and acceptance. Sometimes they get discounts. First commission estimate and final audit amount - those are the two main numbers here to get what is either owed or not owed at the end of the job.

 

[kweaver]

So, is it this? If not, you need to clarify the computation (e.g., what happens with different Estimated and different When Ordered rather than always 100?)

Book1
	A	B	C	D	E	F	G	H	I	J
1				Estimated		When Ordered		Final Audit		Final Commission
2	Commission			$100.00		$100.00		$125.00		$25.00
3	Commission			$100.00		$100.00		$75.00		$(25.00)
4	Commission			$100.00		$100.00		$-		$(100.00)
5	Commission			$100.00		$100.00		$100.00		$-
Sheet3

Cell Formulas
Range	Formula
J2:J5	J2	=H2-F2

 

[FrustratedInWV]

I feel so stupid lol. I need to reverse it.... subtract estimated from the final and that will either be negative or positive.... great thanks! I tend to go from left to right and sometimes you need to reverse that.