Help with math formula

[Karlj]

Hi all i am trying to use the formula from the tax website in excel but i don't know how to remove the numbers behind the decimal i.e the cents. The formula is as follows

Y = AX - B

Y is the tax

A and B are from the tax table

X is the weekly amount earned.

For example
fortnighly pay = 2912.63, To work out the tax we need to turn it in to a weekly amount so divide by 2, this gives me 1456.315. The next step is to remove the cents from the answer and then add .99 cents to it as follows 1456.315 - .315 + .99 = 1456.99

This is where i do not know how to do it. I want it all in the same equation as i am trying to make the cell automatically calculate the tax. Is there a way this can be done either through a formula or through VBA?.

Many Thanks

 

[Karlj]

=ROUND((TRUNC((A5/2),0)+0.99)*(VLOOKUP((TRUNC((A5/2),0)),LU_Scale2,2))-VLOOKUP((TRUNC((A5/2),0)),LU_Scale2,3),0)*2

And is anyone able to help me with this formula i got off the tax website, it does what i want but i am unable to copy and paste it in to my spread sheet without it breaking

 

[Karlj]

Hey all,

Ok so i have managed to get the formula working to an extent but i know need to add in an IF statement.

So the formula i have at the moment is this:

=MROUND(SUM((ROUND(H3/2,0)+0.99)*0.345-162)*2,2)

What i need to do is put it in an IF statement saying that if H3 is greater than 1730 then use x otherwise use y

Is there anyone that could guide me on this please

 

[Norie]

Use INT to remove the decimal part, i.e. the cents.

 

[Karlj]

Thanks Norie for your reply but I'm not sure how you mean to do it, I'm a beginner at excel.

 

[Norie]

If your fortnightly pay is in H3 you could use this formula.


=INT(H3/2)+0.99