Excel help needed...

TheNissanMan

TheNissanMan

Suspended / Banned
Messages
4,189
Name
Ryan
Edit My Images
Yes
Hi peeps,

I need some help with Excel, I am putting a spreadsheet together and need some help with a formula.

In Col A will be either a yes or a no, what I need to do is get a formula that would fill column D with either "Col C*1.175" if it is a Y and "Col C" if it is an N if that makes sense...

Col A Col B Col C Col D

Y 5198.94 4633.94 ???

If anyone could help or if anyone understands my gibberish would be greatly appreciated.
 
I think......... In cell D2 put =IF(A2=Yes,"(C2*1.175)",""C2")

Let me just put that through my Excel, and I'll get back to you.
 
Ruddy heel that was fast thank you....

Next Challenge then :D)

A B C D
Y 10000 9000 11500

And I am not quite sure how to word this! Basically,

A = VAT Qualifying
B = Purchase Price
C = Stand In Value
D = Sale Price

If C is lower than B then we don't pay the VAT on the profit of the vehicle as we purchased the vehicle for more than we are selling it for.

What I would like to do in excel is rather than use a stock sheet have a spreadsheet that can work out my margins for me, easily done if not a VAT qualifier.

What I need the next formula to do is work out that is A = Y then take the VAT of the Sale Price so D/1.175 and subtract C, if C = less than B then I need to divide that figure by 1.175 ?

Does that one make sense ?
 
Another one, I am trying to put another forumla in to say that if the date in Column L is greater than 3 years then = 50 else 0, working on your earlier examples I've tried...

=if(L2=<"TODAY()-1035",50,0)

But it doesn't want to work ????

1035 I know is not quite three years but I need to put a warranty on anything that doesn't have at least three months warranty on it :)

Can you tell it's quiet in the office today!
 
TheNissanMan said:
Another one, I am trying to put another forumla in to say that if the date in Column L is greater than 3 years then = 50 else 0, working on your earlier examples I've tried...

=if(L2=<"TODAY()-1035",50,0)

But it doesn't want to work ????

1035 I know is not quite three years but I need to put a warranty on anything that doesn't have at least three months warranty on it :)

Can you tell it's quiet in the office today!
Click to expand...

Why the quotes? Replace them with brackets and it should work.

I haven't actually got Excel here so can't really help with the other query at the moment (the first one was just off the top of my head)
 
fabs said:
Why the quotes? Replace them with brackets and it should work.

I haven't actually got Excel here so can't really help with the other query at the moment (the first one was just off the top of my head)
Click to expand...

It also needs to be <= rather than =<.

In your second question, what do you want it to do if A isn't Y?
 
jontucker said:
It also needs to be <= rather than =<.

In your second question, what do you want it to do if A isn't Y?
Click to expand...

In the second quote if the car is not over 1035 days it should display 0, and 50 it is over.
 
fabs said:
Why the quotes? Replace them with brackets and it should work.

I haven't actually got Excel here so can't really help with the other query at the moment (the first one was just off the top of my head)
Click to expand...

No joy :(
 
Yup, think it must be the brackets lol, wish I spent more time playing with excel...

Will have another look on Monday as now is definately time for my first day off in 7 :)

Thank you for all your help today and I will be back lol.
 
The formula you need is;

=IF(L2<=(TODAY()-1035),50,0)

I was asking about your second question before, not the third question.
 
Hi Ryan,

If you don't get any joy, I'm happy to knock together a couple of spreadsheets to do these calculations and EMail them to you. It will only take 10 minutes and is probably far easier than trying to explain on here.

:thinking: I'm more concerned at how you're calculating your VAT though . . .

TheNissanMan said:
we don't pay the VAT on the profit of the vehicle as we purchased the vehicle for more than we are selling it for.
Click to expand...

Unless I'm misunderstanding what you're saying, that doesn't sound right.
VAT has nothing to do with whether you made a profit or not.

Any VAT paid on purchases is your input tax, and this may or may not apply to all purchases depending on what they are and where you purchased them from. i.e if you purchase a vehicle from a private individual you won't have paid VAT on it.
If you are VAT registered you must charge VAT on all your qualifying sales regardless of whether you make a profit on them or not.
The overall difference between your input and output tax is the amount you pay or reclaim from hmrc - it's generally not calculated on an item by item basis since it has no impact on your profit margins.
 
Last edited:
+1 on the above. The understanding of VAT behind the calculation sounds off-track. If you buy from private individuals (and hence don't pay VAT on the purchase) and you also buy from auctions or other dealers (where you will pay VAT) then you may need another column or two to track this. If you've bought vehicles from private individuals and sold them for less than you paid for them and not paid the VAT on the basis of this understanding, then you will be owing HMRC some overdue VAT.


Plus, put the VAT percentage in a seperate cell and reference that from within the formula. Otherwise you'll be changing individual formulas in the sheet in the New Year when the rate changes rather than changing one cell and having the sheet update itself.

If you need to assign VAT permanently to each item in the table, include an additional column with the applicable VAT rate. That way your spreadsheet will accomodate a range of dates across the change in rate next year with the ability to assign the correct VAT rate to each item (car?).
 
You must log in or register to reply here.
Back
Top