Forum Discussion
RE: Modern Excel webinars
Hello, I'm looking to include a formula in a cell but without the entry of a value. For example, I would like to be able to enter the value 100 in cell A1 and have the formula in the cell automaticall...
You can just refer to the cell which will contain the value in your formula, i.e. if you are calculating 20% VAT based on value in cell A1, the formula would read =IFERROR($A$1*0.20,””)
The IFERROR part just ensures you dont get an error when cell A1 contains no value.
The IFERROR part just ensures you dont get an error when cell A1 contains no value.
A better way to incorporate a variable like VAT (or Sales Tax) (or ANY other kind of generally stable number that could change) is NOT to hard-code it into any formula. Rather, create a "business table" somewhere else in your workbook, that looks something like this:
and then use Insert.....Name....Apply to apply the labels in the left column to the corresponding values.
Then your formula would read =IFERROR($A$1*VAT,””)
The advantage of using named ranges is that in the future, when VAT or Sales Tax rates change--as such things inevitably do, not frequently perhaps, but nevertheless inevitably--you need only change the rate in one place. Otherwise, you have to change it in all those hard-coded formulas. Not fun.
- ...if you want to add 20% VAT, showing total including cash tax, formula should be multiplied by 1.2. (0.2 gives the tax amount only).
