Excel Help - Multiple If Commands

[Freakbro]

I am trying to automate the calculation of VAT within a table in Excel and have worked out how to calculate if it's standard or zero rated and return the correct VAT amount by using this formula

=IF(Table4[[#This Row],[Rate]]="Standard 20.00%",Table4[[#This Row],[Net]]*20%,0)

Which checks to see if the rate is "Standard 20%" and if not return a 0 (for Zero rated)

But to cover all possibilities, how would I insert a third variable of Reduced Rate VAT into that formula? so the 3 outputs are either

Table4[[#This Row],[Net]] x 20%
Table4[[#This Row],[Net]] x 5%
0

 

[Deleted member 651465]

You can nest the IF function. Use a second IF statement as the false statement of the first.

If(condition=20%,outcome1,if(condition=5%,outcome2))

 

[Freakbro]

So I've now got this

=IF(Table4[[#This Row],[Rate]]="Standard 20.00%",Table4[[#This Row],[Net]]*20%,0, IF(Table4[[#This Row],[Rate]]="Reduced 5.00%",Table4[[#This Row],[Net]]*5%,0))

But that's not working, where am I going wrong?

Edit : Figured it out, thanks @EVH

This

=IF(Table4[[#This Row],[Rate]]="Standard 20.00%",Table4[[#This Row],[Net]]*20%,IF(Table4[[#This Row],[Rate]]="Reduced 5.00%",Table4[[#This Row],[Net]]*5%,0))

now works

 

[james.miller]

If you have 2019 or O365 and aren't fussed about backwards compatibility then use IFS as it removes the need for nesting:

https://support.office.com/en-gb/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

 

[touch]

The best way to do it would be to create a little lookup table somewhere which holds all the vat rates.

Name       Rate
Standard   20
Reduced    5
Zero       0

Then add a column which holds the vat rate for each row (and populate it by looking up the value from the table). Then to get your Net column you just mutiply by the new rate column.
That way, if any of the vat rates ever change you just change it in one place and dont need to go through changing all the formulae.

 

[Martynt74]

If you have 2019 or O365 and aren't fussed about backwards compatibility then use IFS as it removes the need for nesting:

https://support.office.com/en-gb/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

Wow, glad i saw this post!

Great stuff.

 

[fretted]

The best way to do it would be to create a little lookup table somewhere which holds all the vat rates.

Name       Rate
Standard   20
Reduced    5
Zero       0

Then add a column which holds the vat rate for each row (and populate it by looking up the value from the table). Then to get your Net column you just mutiply by the new rate column.
That way, if any of the vat rates ever change you just change it in one place and dont need to go through changing all the formulae.

This is the better option and allows maintainability and can cater better for partial exemptions and partial recovery situations

 

[wesimmo]

The best way to do it would be to create a little lookup table somewhere which holds all the vat rates.

Name       Rate
Standard   20
Reduced    5
Zero       0

Then add a column which holds the vat rate for each row (and populate it by looking up the value from the table). Then to get your Net column you just mutiply by the new rate column.
That way, if any of the vat rates ever change you just change it in one place and dont need to go through changing all the formulae.

This is the way I'd go.

Much easier to update if you want more tax bands in the future and much easier to write and understand the formula.

My advice is normally try and avoid massive nesting of formulas anyway.