IF Formula in Excel

S

Speedwell

Registered User
Messages
270
Hi

Can anyone help me do a formula in excel. I'm in work at the mo and am completely stuck. :confused:

I am looking to do a formula to calculate VAT. Instead of having 3 diff columns for each VAT amt I was hoping to have 1 column with 1 formula in it to calculate the VAT.

For example, if cell G3=1, then the VAT is 0, if G3=2, then VAT is 13.5% and if G3=3, then VAT is 21%. i.e, when I enter 1 2 or 3 it will calculate the VAT for me.

When I did this with the IF function it only allowed me do a 2 bracket formula and I need 3 for 0%. 13.5% and 21%.

Not sure if I have written this clearly but if anyone out there could help me construct a formula for the above I would be really grateful.

Thanks and Happy Easter!
 
Try the following formula

=IF(G3=1,0%,IF(G3=2,13.5%,IF(G3=3,21%,"error")))
 
Thanks for answering.

The only thing is that it does not multiply original cell F3 by the VAT% to give total amount in cell H3. Does that make sense?

Thanks.
 
=IF(G3=1,0%,IF(G3=2,13.5%,IF(G3=3,21%,"error")))



Vat amount: Replace 13.5% with (F3*0.135) and 21% with (F3*0.21)
=IF(G3=1,0%,IF(G3=2,(F3*0.135),IF(G3=3,(F3*0.21),"error")))


Amount Including VAT (replace 0.135 with 1.135 and 0.21 with 1.21
=IF(G3=1,0%,IF(G3=2,(F3*1.135),IF(G3=3,(F3*1.21),"error")))
 
Ajapale,

Thanks so much for helping.

When I entered the forumla, it calculates the VAT (great!) but I also want it to add that VAT amt and give me the total value of net amt and VAT amt in that same cell.

Also, does it have to have "error" at the end of the formula?
 
Ive edited my original post to show how to get the total amount (1.135 instead of 0.135).

You dont need the "error" message but it is good practice if you are going to copy and paste the formula a lot.

You coulld replace "error" with " ". That would put a blank space in the cell.

There are some great web sites out there about excel: just google something like "ms excel tips tricks"

aj
 
While ajapale's solution undoubtedly works, it may well be very difficult for others to follow. Who's going to manage this spreadsheet when you are on holidays or when you leave?

You might be better having multiple columns, showing each stage of the calculation, and just hide the unnecessary columns when printing.
 
Personally, rather than entering the 1/2/3 to represent the different VAT figures... I'd just enter the VAT figure directly in its own column.

Then, in the total cost(/whatever) column enter "=F3*(1+G3)"

This should be very simple for anyone stepping in to understand.

(do make sure that the VAT figure is a cell which is formatted to percentage and not entered as text with a percentage sign... IYKWIM)
 
You must log in or register to reply here.
Back
Top