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.001. 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 SurchargesSurcharge 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).

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.

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 SurchargesSurcharge 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!

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

It worked great, thanks!

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History