Multiple IF Statements with < and > functions for range of values

Hi There,

I have a sheet used to calculate USC levies in Ireland for payroll purposes. I am tryng to use one formula to calcalate amount due. At present i have it set up manually. If there was an IF statement i could use to make thngs easier i would appreciate your help. I have attached sample sheet to help understand what im looking for.

Basically in short i would like column k "Total Due" to include IF statement for 3 conditions rather than columns E-J in blue.
If gross wage <€193 then calculate at a rate of 2%
If >€193.01 but <€308 calculate first €193 at 2% remainder at 4%
If >308.01 calculate first €193 at 2%, between €308-€193.01 at 4% and remainder >€308.01 at 7%. This is on cummulative basis and for week1 so

Week2 will be as follows
If gross wage <€193*2 then calculate at a rate of 2%
If >€193.01*2 but <€308*2 calculate first €193*2 at 2% remainder at 4%
If >308.01*2 calculate first €193*2 at 2%, between (€308*2)-(€193.01*2) at 4% and remainder >€308.01*2 at 7% as so on *3, *4, *5..........*52 weeks

Try

=SUMPRODUCT(--(A2>{0;193;9999}), (A2-{0;193;9999}), {0.02;0.02;0.07})

and

=SUMPRODUCT(--(A2/2>{0;193;9999}), (A2/2-{0;193;9999}), {0.02;0.02;0.07})

Originally Posted by Ace_XL

Try this...

=MIN(D9,$D$3*$B9)*$E$3+MIN(MAX(D9-$D$3,0),($D$4-$D$3)*$B9)*$E$4+MAX(0,D9-($D$4*B9))*$E$5

Tried your formula Ace_XL and works a charm. Gonna use this for other taxes to create a tax deduction card for the year rather than using a payroll package. Cheers you have saved me alot of time and money.
I wish i could owe you back but i still have so much to learn on excel.