Ask Your Question

Add Vat to some items but not others in a column...

asked 2019-11-19 15:02:33 +0200

lucky67 gravatar image

updated 2019-11-19 23:07:05 +0200

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? C:\fakepath\Temp eg VAT sheet.ods

edit retag flag offensive close merge delete


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

Opaque gravatar imageOpaque ( 2019-11-19 15:22:11 +0200 )edit

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 gravatar imagelucky67 ( 2019-11-19 23:16:44 +0200 )edit

test again for email alert

lucky67 gravatar imagelucky67 ( 2019-11-19 23:23:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-11-20 00:23:06 +0200

Opaque gravatar image

updated 2019-11-20 00:28:41 +0200


see the following file:


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.

edit flag offensive delete link more


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 ...(more)

lucky67 gravatar imagelucky67 ( 2019-11-20 12:13:25 +0200 )edit

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

Opaque gravatar imageOpaque ( 2019-11-20 12:46:03 +0200 )edit

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

lucky67 gravatar imagelucky67 ( 2019-11-20 23:44:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-19 15:02:33 +0200

Seen: 56 times

Last updated: Nov 20 '19