VAT for second-hand goods

**Patrick_kingshott** · 12-20-2011, 03:56 PM

Hi, I have been trying to work out a formula to work out the VAT on second hand goods bearing in mind second hand VAT is on charged on the difference between Purchase Price (PP) and Sale Price (SP).

I have worked out an "IF" equation which works perfectly unless the SP is less than the PP then I get a negative value which I need to show as Zero.

Essentially I need to include within an IF equation

IF(PP<=SP),0, (MY IF EQUATION)

However putting this in doesn't work for me.

Please can someone whow me the error of my ways and suggest a solution which includes the equation.

Thanks in advance

Kind Regards Patrick

**Paul** · 12-20-2011, 04:00 PM

Hi Patrick, welcome to the forum.

Nested IF statments must be in the form:

=IF ( comparison , true , false )

You can replace the true, false, or both, with additional IF structures, e.g.

=IF ( comparison , true , IF ( comparison #2 , true , false ) )

What is "MY IF EQUATION"? We can help you put it together if you aren't getting it quite right.

**Patrick_kingshott** · 12-20-2011, 04:08 PM

Thanks for the lightening fast reply!

My IF equation is:

=IF(TOTAL SALE PRICE>=(INITIAL SALE PRICE-PURCHASE PRICE),(INITIAL SALE PRICE-PURCHASE PRICE)/120*20,(TOTAL SALE PRICE)/120*20)

TOTAL SALE PRICE & INITIAL SALE PRICE will always been the same unless the transaction is a refund then it works the VAT out based upon what VAT was charged in the first place.

I hope you can understand my attempt at accounting!

Kind Regards Patrick

**Paul** · 12-20-2011, 04:29 PM

Between your first and third posts, there's two sale prices: TOTAL sale price and INITIAL sale price. I'm not sure which you're referencing in the first post, but the additional IF test would resemble:

Please Login or Register  to view this content.

**Patrick_kingshott** · 12-20-2011, 04:45 PM

That is exactly what I was looking for!

Can we go one step further and include if another cell doesn't say "United Kingdom" then the VAT is also 0

That would completely foolproof my spreadsheet!

Kind Regards Patrick

**Paul** · 12-20-2011, 04:52 PM

Sure, change the beginning of my formula to:

Please Login or Register  to view this content.

**Patrick_kingshott** · 12-20-2011, 05:08 PM

You have saved me hours of racking my brain and countless headaches.

This is such a good forum!

Thanks so much for your swift help, you're a legend!

Kind Regards Patrick