Excel Formula (VAT)

MarkLP

MarkLP

MarkLP

Soldato
Joined
28 Sep 2004
Posts
3,128
Location
Devon, UK
Hi, please can someone help with this formula! I can't seem to work it out at all.

I've got a figure of £5.00 in one cell, I then want to put the vat of that £5.00 in another cell.

so

TOTAL | VAT
------------------
£5.00 | £0.74


Whatever I do I always seem to get £0.88 as the vat :confused:

If anyone can tell me the formula it would be much appreciated!

Cheers,
Mark
 
MarkLP said:
Whatever I do I always seem to get £0.88 as the vat :confused:

If anyone can tell me the formula it would be much appreciated!
Click to expand...
What are you using to get 0.74?! :confused:

Say they're in A2 and B2, the formula is =A2*0.175. Rounded up, that gives £0.88
 
Thanks, I was using a Vat Calculator I downloaded, must have been wrong then :confused:

Now I'm stuck with an IF formula. What I want to do is, if the cell = Y then it adds £2.00 to another cell.

I have tried 2 formulas

Code: 
=IF(G8:G20="Y",D23+2,0) and =IF($G$8:$G$20="Y",D23+2,0)

But I just seem to get #VALUE! come up :confused:

Any ideas please?

Cheers,
Mark
 
Code: 
=IF(G8:G20="Y",D23+2,0) and =IF($G$8:$G$20="Y",D23+2,0)

Why the G8:G20? Surely you'd want something like:

Code: 
=IF(G8="Y",D23+2,0)

Depending on which cell it is that has the Y value in.

If that's not what you need, then can you describe it a bit more?
 
csmager said:
Code: 
=IF(G8:G20="Y",D23+2,0) and =IF($G$8:$G$20="Y",D23+2,0)

Why the G8:G20? Surely you'd want something like:

Code: 
=IF(G8="Y",D23+2,0)

Depending on which cell it is that has the Y value in.

If that's not what you need, then can you describe it a bit more?
Click to expand...

I want the IF formula to cover the Cells G8 to G20, so if I type "Y" in any of those cells it will add £2.00 to cell D23.
 
MarkLP said:
I want the IF formula to cover the Cells G8 to G20, so if I type "Y" in any of those cells it will add £2.00 to cell D23.
Click to expand...
Don't think that's possible without having 12 nested IF statements.

Maybe you could say what you're trying to do and we could see if there's any different ways of approaching it?

If you want to achieve the same thing, you could have a whole column of IF statements like the one I suggested that, if the column next to it ="y", then it puts a 1, if not then a 0.

You could then SUM that column, and put an IF statement to say IF the SUM>0 then D23+2 else 0. Or something...
 
csmager said:
Don't think that's possible without having 12 nested IF statements.

Maybe you could say what you're trying to do and we could see if there's any different ways of approaching it?

If you want to achieve the same thing, you could have a whole column of IF statements like the one I suggested that, if the column next to it ="y", then it puts a 1, if not then a 0.

You could then SUM that column, and put an IF statement to say IF the SUM>0 then D23+2 else 0. Or something...
Click to expand...

Thats True hadn't thought of that!

Thanks very much for the help guys and the idea!

Cheers,
Mark
 
If the total price (including VAT) is £5, then the price before VAT is

500 / 1.175 which is £4.25 or so, so that's the £0.74/75

If you put the base price as £5 and try to add VAT to it you'll find it's about £0.88 so that's where the figure's coming from.
 
MarkLP said:
Thanks, I was using a Vat Calculator I downloaded, must have been wrong then :confused:

Now I'm stuck with an IF formula. What I want to do is, if the cell = Y then it adds £2.00 to another cell.

I have tried 2 formulas

Code: 
=IF(G8:G20="Y",D23+2,0) and =IF($G$8:$G$20="Y",D23+2,0)

But I just seem to get #VALUE! come up :confused:

Any ideas please?

Cheers,
Mark
Click to expand...

Try This

=IF(OR(G8:G20="Y"),D23+2,0)
 
Nash said:
If the total price (including VAT) is £5, then the price before VAT is

500 / 1.175 which is £4.25 or so, so that's the £0.74/75

If you put the base price as £5 and try to add VAT to it you'll find it's about £0.88 so that's where the figure's coming from.
Click to expand...

So the program I was using all along was correct. I did put in my first post that £5 was with the vat, but that doesn't matter.

So I want, in the VAT cell, TOTAL / 1.175?

EDIT/ Actually that goes in the Exec Vat, then I'll do a formula TOTAL-Exec Vat then that will give me the VAT value :)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom