Solved Double-Surcharge apply on an amount

July 5, 2011 at 09:19:36
Specs: Windows 7, Intel Core2Duo/8GB
	A	   B	      C	          D
1	$1,250.00	   $6,250.00   $12,500.00   $5,000.00
2	    $0.00	     $562.50	$875.00	 SUB
3	$1,250.00	   $6,812.50   $13,375.00	

1. A1, B1, C1 are examples of T&M Totals
2. D1 is the limit on which Surcharge is based on
3. A2, B2, C2 are Surcharges for Subcontractors
4. A3, B3, C3 are Totals including Surcharges

Surcharge is "+10% on First $5000 and 5% on Excess of $5000" if D2=SUB, otherwise Surcharge is $0. The formula will be used in A2, B2 OR C2.

I have the following formulas in A2, B2 and C2 consecutively without the SUB criteria:
=IF(A1>5000,((5000*0.1)+(A1-5000)*0.05),0)
=IF(B1>5000,((5000*0.1)+(B1-5000)*0.05),0)
=IF(C1>5000,((5000*0.1)+(C1-5000)*0.05),0)

These formulas will work when D2=SUB, but not D2=CON (Surcharge does not apply).


See More: Double-Surcharge apply on an amount

Report •

#1
July 5, 2011 at 10:27:28
Please edit your post for 2 items:

1 - Your columns don't line up correctly.
2 - Please refrain from using all upper case text. All upper case is the internet equivalent of shouting, and we don't like to be shouted at.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 5, 2011 at 23:29:17
Thank you for your response. My data is as follows:

	A		B		C		D
1	$1,250.00	$6,250.00	$12,500.00	$5,000.00
2	$0.00		$562.50		$875.00		SUB
3	$1,250.00	$6,812.50	$13,375.00

1. A1, B1, C1 are examples of T&M Totals
2. D1 is the limit on which Surcharge is based on
3. A2, B2, C2 are Surcharges for Subcontractors
4. A3, B3, C3 are Totals including Surcharges

Surcharge is "+10% on First $5000 and 5% on Excess of $5000" if D2=SUB, otherwise Surcharge is $0. The formula will be used in A2, B2 OR C2.

I have the following formulas in A2, B2 and C2 consecutively without the SUB criteria:
=IF(A1>5000,((5000*0.1)+(A1-5000)*0.05),0)
=IF(B1>5000,((5000*0.1)+(B1-5000)*0.05),0)
=IF(C1>5000,((5000*0.1)+(C1-5000)*0.05),0)

These formulas will work when D2=SUB, but I'd like to make it also works when D2=PRM (when Surcharge does not apply), answer then should show $0 as below:

	A		B		C		D
1	$1,250.00	$6,250.00	$12,500.00	$5,000.00
2	$0.00		$0.00		$0.00		PRM
3	$1,250.00	$6,250.00	$12,500.00

Please advise. Thanks!

Report •

#3
July 6, 2011 at 08:35:55
✔ Best Answer
=IF($D$2="PRM",0,IF(A1>5000,((5000*0.1)+(A1-5000)*0.05),0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 6, 2011 at 12:33:55
It worked great, thanks!

Report •

Ask Question