Formula to calculate contact base with 20% threshold credit or bill

[HColeman]

I have a contract base price of $4.00 with a 20%threshold to credit or bill customer if the actual price of a product is 20% less or greater than the contact base price. The amount to bill the customer will be the difference of the 20% threshold amount and actual price. I need a formula to calculate this.

If I put the contract base price of $4.00, in cell C3
And, the actual purchase price of $5.25 in cell C4
What formula can I use to calculate the billable amount above or below the threshold amount?

Example:

Base Price $4.00
Purchase Price $5.25
That is 20% greater than the base price.
I need the formula to calculate $5.25 - $4.80

 

[Dave Patton]

T202209a.xlsm
	A	B	C	D
1
2
3		Contract	4.00	20%
4		Actual	5.25
5		Difference	0.45
6
3a

Cell Formulas
Range	Formula
C5	C5	=C4-C3*(1+D3)

 

[HColeman]

The formula does given above works if the amount is greater than the 20% threshold, but does not work if it is less than the 20% threshold. I need for the formula to calculate both ways.

Example 1 Greater than the 20% threshold:
Base Price $4.00
Purchase Price $5.25
20% Above Threshold - Anything above $4.80
Difference .45

Example 2 Less than the 20% threshold
Base Price $4.00
Purchase Price $3.00
20% Below Threshold - Anything Below $3.20
Difference (.20)

What formula would I use to calculate both? I think it would be an IF formula, but not sure what.

 

[Dave Patton]

What formula did you try?

T202209a.xlsm
	A	B	C	D
1
2
3		Contract	4.00	20%
4		Actual	5.25
5		Difference	0.45
6
3a

Cell Formulas
Range	Formula
C5	C5	=IF(C4>C3,C4-C3*(1+D3),C4-C3*(1-D3))