sheet to automatically add vat to the commercials sale price

[holty]

Hi All

Typical Newbie !

So I have a sheet that I need to add vat to only certain products (Commercial vehicle types)

We sell cars and vans and I need the sheet to automatically add vat to the commercials sale price

so im trying to get the formula to look at a cell which would have maybe 10 different names (Relay, Berlingo, Dispatch etc etc)(Cars do not attract VAT and obviously have different names) which would then add 20% to the sale price

Hope that make sense, wont let me add images !!!

"The following errors occurred with your submission
You are not allowed to post any kinds of links, images or videos until you post a few times".


Many thanks in advance

[TMS]

Just so you know I’m not trying to be unhelpful, ... you could list the vehicles that attract VAT in a separate sheet and then use COUNTIF to check if they are there. That beats a very long nested IF. And you're going to have to list them somewhere anyway.


Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional.)

[Pete_UK]

You can do this by having a list of those names in a column somewhere and then using an IF function to find if the name on a particular row exists in that list, and apply VAT if it does. It's difficult to recommend a particular formula, however, as you have given no details of the columns that you use.

It would help if you attached a sample Excel workbook, and you can do this even with only 1 post.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

You should also change your thread title so that it reflects what you are trying to do - they are quite strict about that on this forum (see Rule 01).

Hope this helps.

Pete

[TMS]

Oh, and please note ...

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[holty]

Thank you all for the replies

Sorry I broke the rules on my first post, apologies

Could i ask what title would be ok for this post as I cant think of one as Im not up to speed with all the functions etc

Attached a copy of the sheet im doing

was thinking of a drop down box of models ? maybe ?

[AliGW]

Please change your title to this: "sheet to automatically add vat to the commercials sale price".

[holty]

Done, Title changed now,thank you

[Pete_UK]

Assuming you listed the names of vans in column U (which you have shaded grey), then you could use this formula in I13:

=IF(B13="","",IF(COUNTIF(U:U,B13),H13*20%,0))

Copy down as required.

Incidentally, you don't need to use SUM and + in the same formula. For example, in J13 you could use this:

=SUM(H13:I13)

which will avoid the errors that you are getting now by trying to add a null string.

Hope this helps.

Pete

[holty]

Thank you for that, appreciate the rapid help

Regards

Phil

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[holty]

Hi
Theres a bit more Im afraid
Ive attached a sheet

So Comercial vehicles attract vat on the sale price at 20%
We dont pay VAT on any profit

Cars however are different, we pay vat on the gross margin from the purchase price to the sale price ie
Bought for £9000 sold for £10000 therefore we pay £166.67 as a cost
However if we buy for £9000 and sell for £8500 we dont get vat return for the £500 it goes as zero
(yes we do lose money selling cars!)

So on my sheet Ive been manually adjusting the costs of commercials by entering a minus figure equal to the vat on the sale price (o13)
Then zeroing R13 Ideally I would like this to be done auto

On cars the issue I have is I have to manually zero the vat if it shows as a loss !

Hope this makes sense and someone can help me please

Thanks in advance

[TMS]

You're welcome. Thanks for the rep.

[AliGW]

Where and in which of the two workbooks attached should helpers look?

[holty]

Where and in which of the two workbooks attached should helpers look?

Ive highlighted the fields that Im struggling with, sorry both sheets are the same, didnt appear to load the first one so duplicated

[JeteMc]

I am still not sure that I fully understand, however I'll take a shot. (Based on Show File.xlsx)
1. For column O: =IF(ISNUMBER(MATCH(B13,V$12:V$17,0)),I13-I13,"")
2. For column R: =IF(N13-Q13<0,0,N13-Q13)
3. For column T: =SUM(J13-S13,O13)
If this doesn't work, please tell us the values that you expect to see in rows 13:15. Unless the rational behind those values should be obvious to a person whom has no experience with VAT, please explain why those values are expected.
Let us know if you have any questions.

[holty]

Thank you, will get back to you shortly when Ive tried them
I know the motor trade and VAT is a minefield Im afraid

Appreciate your time and effort

[holty]

I am still not sure that I fully understand, however I'll take a shot. (Based on Show File.xlsx)
1. For column O: =IF(ISNUMBER(MATCH(B13,V$12:V$17,0)),I13-I13,"")
2. For column R: =IF(N13-Q13<0,0,N13-Q13)
3. For column T: =SUM(J13-S13,O13)
If this doesn't work, please tell us the values that you expect to see in rows 13:15. Unless the rational behind those values should be obvious to a person whom has no experience with VAT, please explain why those values are expected.
Let us know if you have any questions.

Hi
Sorry for the long delay, just away from work, so Ive put up the sheet with your formulas in and in Red shade the expected result
Thanks again

So Commercial vat is not a cost as the customer pays the Vat on the sale price
ie £15000 plus 20% vat
we bought the goods at £14000 plus 20% vat
Difference is £1000 Gross Profit less any costs (£50 on the sheet attached)
No Vat is paid on the profit on Commercials because of the added sales VAT
Therefore the nett Gross is £950

On a car using the same figures
Sold at £15000
Bought at £14000
Profit £1000 therefore we pay the vat content which is £166.67 which becomes a cost so therefore to work out nett profit it would be
Cost £14216.67 (vat at 166.67 + 50 cost)
Nett Profit = £783.33

Hope this makes sense

TIA

[JeteMc]

The first difference seems to be in column N where the formula displays 4,000 however the highlighted value suggests it should be 10,000.
Please explain the discrepancy in terms such as: Since this is a commercial vehicle I need to calculate using … instead of the difference between the values in columns J and M.
Let us know if you have any questions.

[JeteMc]

The following formula, placed in cell T13, will provide the expected values in T13:T14 =IF(ISNUMBER(MATCH(B13,V$12:V$17,0)),H13-M13-P13,SUM(J13-S13,O13))
I still feel as if I am not understanding. I believe that it would be best to proceed column by column.
Let us know if you have any questions.