Add Vat to some items but not others in a column

[lucky67]

This isn’t desperately important but I am sure there must be a way to do this and if I knew how I think I could use the idea for other things I do. It would be useful knowledge for me to have…

At the moment I have columns with the unit cost, no of units, total exc vat amount, then a ‘vat’ column containing 1 or 1.2 (1 is for zero rated, 1.2 is for liable for 20% VAT)
Then an inc Vat column which is just the total ex vat column multiplied by the Vat column. Which gives the right numbers - it works…
However it is easy to read the VAT column as the no of units column. (So 1 unit not 5 etc)
I was just going to hide the Vat column -but then it also needs to be obvious whether I thought the item was liable for VAT or not. I could do some background colour or add an additional column containing Zero or 20% for info, not included in any calculations.
But I am sure there is a way to do something like =G3 * (if E3 = ‘20%’ 1.2 , if E3 = “Zero” 1)
Is there?
Temp eg VAT sheet.ods

[anon73440385]

Please provide an anonymized sample file (hard to comprehend from description only).

[lucky67]

Sorry I didn’t see your comment…I thought I’d got it so I would get an email me when someone replied
I’ve edited my post and attached a file - hopefully that helps.
In the file there are two examples - the current one (except the VAT column isn’t normally highlighted)
and what I’d ideally like it look like…with the calculation in column H being dependent on the text in column E.

[lucky67]

test again for email alert

[anon73440385]

Hello,

see the following file:

VAT-Rate.ods

The problem is: You seem to to have a little misconception about what percent and formatting is. A percent number is - nomen est omen - what it says: per hundred → thus 1% means 1/100. Therefore 20% means 20/100 = 0,20. In other words: Percent is neither a special data type nor a text, but a decimal number. Now formatting enters the scene. Formatting is in some sense an instruction (to LibreOffice) how to present the numbers to the user. And formatting as percent means. Present a value stored as 0,20 and formatted as Percent as 20%.

Therefore the solution is quite simple:

1. Format your VAT column E as percent (Format Code: 0" "% --or-- 0%)
2. Add the numbers 20 or 0 which shows as 20% and 0%m but in fact is stored as 0,20 and 0 in Calc
3. Use the formula VATincl = VATexcl*(1+Percent)

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark () next to the answer.

[lucky67]

Thanks - that works and is much simpler than my plan - I didn’t realise that the percent number format existed - or rather worked the way it does (ie you enter the value and it shows as a percent but calculates as decimal…I thought you had to work out and enter the decimal value in calculations) I wish I’d discovered that years ago - I don’t use percentages much nowadays but used to.
Have a minor query on this - is it possible to show 0% in the column without showing all zero values in the sheet?
And out of interest - is there a way of doing what I thought I needed to do?
I guess I mean doing what the percentage format is doing but with parameters I set myself
So enter eg text in a cell but tell calc to treat that as a value?

(and my email notifications are still not working!)
Thanks again …

[anon73440385]

So enter eg text in a cell but tell calc to treat that as a value?

If you enter 20% calc automatically recognizes this as number, just have a look to the immediate format change of the cell at Right click -> Format Cells or CTRL+1. Thus it is already doing what you want, with one drawback: It formates the value using 2 decimal digits, thus you may want to change the format to zero decimal digits (format code 0% (see also Right click -> Format Cells and Option Decimal places. And this also answer your second question about decimal places of the percentage - just use format code 0% in Format .-> Cells -> Tab: Numbers -> Category: Percent

[lucky67]

Thanks - I’ve got it - completely understand.

What I meant by this
So enter eg text in a cell but tell calc to treat that as a value?
is for something that isn’t a percentage…not sure if I can explain what I mean.
Say Lunch is £2, dinner £5. I have one column with no of visits, in another I can write lunch or dinner. And in the total column I have no of visits * either £2 or £5 depending on the text (lunch or dinner)
This is just a random example and I know how I could do this using another column etc but was just wondering if it was possible from just the text.
Anyway - my immediate problem with the percentages is solved - next time I am doing something where this would be helpful I’ll post another question.
thanks so much again