SC Has a tax cap system of 5% up to $300 max, so anything $6000 plus is capped at $300. This includes installed accessories. So if the combined price of the unit plus installed accessories is under $6000 then 5% sales tax applies, else if over $6000, tax stops at 300.
I attached an example quote sheet. It does not have everything on it, but the main segment.
Is there a way to identify matching numbers in cells (K 4-17) and (L 4-17), have them look up the corresponding unit (P 4 -17 ) and acc (F 4 - 17) prices, total those prices and verify the tax cap rule. If it is under $6000 like unit 3, then follow display of unit three. If over $6000 like unit 4, then 0 out R14,R15 and make Q13 $300.00?
If you look at the Exampe sheet and look at the code idea below, I hope this will be a good enough description of what I would like to do.
Unit Tax Formula (Q4:Q17)
CURRENT EQUATION
=IF(((G13="UNIT")*AND(I13="AG")),(P13*0),IF((G13="UNIT")*AND(H13="OFF"),(P13*C51),IF((G13="UNIT")*AND(H13="ON"),IF(P13<6000,(P13*D21),D22)))*(A13))
ADD TO FORMULA SOMEHOW -- RULE ONLY APPLIES TO “ON”
=IF(((G13="UNIT")*AND(I13="AG")),(P13*0),IF((G13="UNIT")*AND(H13="OFF"),(P13*C51),IF((G13="UNIT")*AND(H13="ON"),+ if K13 HAS A NUMBER, (EX 4) LOOK FOR SAME NUMER (4) IN L4 - L17 THEN ADD MATCHING NUMBERS VALUES TOGETHER : UNIT 4 (GET VALUE FROM P13 ($5568)) + ACC 4 = L14 (GET VALUE FROM F14 ($199.99)) AND L15 (GET VALUE FROM F15 ($255)), IF(P13<6000,(P13*D21),D22)))*(A13))
Result would be 300 since matching total = 6032.99
Acc Inst Tax – On Fromula (R4:R17)
Current Equation
=IF(((G14="ACC")*AND(J14="Y")*AND(H14="ON")),(F14*D21),0)*A14
ADD TO FORMULA SOMEHOW -- RULE ONLY APPLIES TO “ON”
If L14 HAS A NUMBER AND IT MATCHES TO A UNIT NUMBER IN K THEN “0.00” ELSE =IF(((G14="ACC")*AND(J14="Y")*AND(H14="ON")),(F14*D21),0)*A14
Bookmarks