Calculation for VAT applicable

[agnesgough]

Hi all

This probably basic for everyone but me!!!

I am trying to write a formulae for quick calculations if a item has vat or not. This is what I have written.

=IF((C2=Nox0.0)ORIF(C2=Yesx0.2))

Any help gratefully appreciated.

Ags

 

[VoG]

Maybe something like

=IF(C2="No",0,A2*0.2)

 

[agnesgough]

Thanks for that but mabye I am doing this all wrong. Here is the sheet I am trying to do. I want to be able to select yes/no and then put in the amount under the relevant heading and it then works out the calculation based on the yes/no for the item, vat and total.

So I put in 600 for a database cost and select yes, it then deducts the 20% off the 600 , puts the 20% in the VAT colum and the total ends up as 600. Does that make sense?

<table style="width: 728px; height: 42px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 23pt;" width="30"> <col style="width: 58pt;" span="2" width="77"> <col style="width: 34pt;" width="45"> <col style="width: 73pt;" width="97"> <col style="width: 56pt;" width="74"> <col style="width: 78pt;" width="104"> <col style="width: 48pt;" width="64"> <col style="width: 47pt;" width="63"> <col style="width: 44pt;" width="58"> <col style="width: 44pt;" width="59"> <col style="width: 48pt;" width="64"> <tbody><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 23pt;" height="20" width="30">
</td> <td style="width: 58pt;" width="77">Date </td> <td style="width: 58pt;" width="77">VAT</td> <td style="width: 34pt;" width="45">Petrol</td> <td style="width: 73pt;" width="97">Ofice Supplies</td> <td style="width: 56pt;" width="74">Telephone</td> <td style="width: 78pt;" width="104">Gas and Electric</td> <td style="width: 48pt;" width="64">Database</td> <td style="width: 47pt;" width="63">Software</td> <td style="width: 44pt;" width="58">Services</td> <td style="width: 44pt;" width="59">VAT</td> <td style="width: 48pt;" width="64">Total</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> <td class="xl63" align="right">07/06/2011</td> <td class="xl63">No</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">600</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td>
</td> <td class="xl63">Yes</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">600</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[VoG]

A formula cannot subtract a value from one cell and put it in another. You would need to enter the price without VAT then use formulas to calculate the VAT and the total

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	VAT	Petrol	Ofice	Supplies	Telephone	Gas	Database	Software	Services	VAT	Total
2	07/06/2011	No							600		0	600
3		Yes							600		120	720
Sheet9

 

[agnesgough]

Hi Peter

Thank you so much. I would offer to have your babies in return but way too busy screaming at my computer

THANKS

Ags

 

[agnesgough]

What formulae do I use to say if yes use this and if no use this formulae. so a or formulae. Will have a football team if you can help with that

Ags

 

[VoG]

Try like this

=IF(C2="Yes",J2*0.2,IF(C2="No",0,""))

 

[agnesgough]

Nope that doesn't seem to work either, it works if it is no but not for yes


ags

 

[VoG]

It works here

Excel Workbook
	C	N
1	VAT
2	No	0
3	Yes	120
4	Other
Sheet9