Skip to main content
Solved

Conditional formula to determine VAT output

  • April 12, 2019
  • 5 replies
  • 106 views
W

Hello all :slightly_smiling_face:

I’m trying to work out a formula that establishes whether to charge VAT on a product or not but can’t seem to get the IF/AND/OR statement correct.

The conditions that I require are as follows:

If Gender is equal to “Boy’s” or “Girl’s” then 0%
or
If Gender is equal to “Men’s” and Size is less than 6.5 then 0%
or
If Gender is equal to “Women’s” and Size is less than 5.5 and Heel Height is less than 4 then 0%
Else
20%

I’m stuck in a bit of a black hole trying to figure this one out - any suggestions/examples on how to get this going or where to get started would be most appreciated!

Best answer by Jeremy_Oglesby

I think this should do it for you:

SWITCH(
   Gender,
   "Boy's", 0,
   "Girl's", 0,
   "Men's", IF(Size < 6.5, 0, 20),
   "Women's", IF(AND(Size < 5.5, {Heel Height} < 4), 0, 20),
   20
)

The SWITCH() function can often be easier to use than a whole bunch of IF() statements, if all of the IF() statements are dealing with the value of the same field – so in this case, we can replace any IF() statements that are dealing with the “Gender” field with a single SWITCH() function.

The way this works is that it looks at the “Gender” field, and if it’s value matches “Boy’s”, it returns the output followed by “Boy’s”, namely 0. If the value of “Gender” does not match “Boy’s” it moves on to the next value to check it against, “Girl’s”… and so on. In the case of matching “Men’s” or “Women’s”, then the SWITCH() will return the value returned by the IF() statements following the matching value.

And that last 20 at the end is what will be returned if the value of “Gender” is blank or does not match any of the options listed in the SWITCH() function – you may want to remove that if it’s not your desired default value.

B

5 replies

J
Forum|alt.badge.img+18

I think this should do it for you:

SWITCH(
   Gender,
   "Boy's", 0,
   "Girl's", 0,
   "Men's", IF(Size < 6.5, 0, 20),
   "Women's", IF(AND(Size < 5.5, {Heel Height} < 4), 0, 20),
   20
)

The SWITCH() function can often be easier to use than a whole bunch of IF() statements, if all of the IF() statements are dealing with the value of the same field – so in this case, we can replace any IF() statements that are dealing with the “Gender” field with a single SWITCH() function.

The way this works is that it looks at the “Gender” field, and if it’s value matches “Boy’s”, it returns the output followed by “Boy’s”, namely 0. If the value of “Gender” does not match “Boy’s” it moves on to the next value to check it against, “Girl’s”… and so on. In the case of matching “Men’s” or “Women’s”, then the SWITCH() will return the value returned by the IF() statements following the matching value.

And that last 20 at the end is what will be returned if the value of “Gender” is blank or does not match any of the options listed in the SWITCH() function – you may want to remove that if it’s not your desired default value.

JonathanBowen
B
W
JonathanBowen
Forum|alt.badge.img+18

Hi @Will_Bailey - it is possible to do this in a single formula, but I find it is much easier to break this problem down into interim steps/fields before coming up with the final answer. Have a look at this:

I’ve got 4 formula fields in this table:

Boys/Girls - determines if gender is boys or girls with the formula:

IF(OR(Gender = 'Boys', Gender = 'Girls'), 'yes', 'no')

Mens Zero Rated - determines if a Mens item should be zero rated with the formula:

IF(AND(Gender = 'Mens', Size < 6.5), 'yes', 'no' )

Womens Zero Rate - determines if a Womens item should be zero rated with the formula:

IF(AND(Gender = 'Womens', Size < 5.5, {Heel Height} < 4), 'yes', 'no')

The final column - VAT Rate - then uses a formula to say if any of the other 3 columns are “yes” then use 0% otherwise use 20%, using the formula:

IF(OR({Boys/Girls} = 'yes', {Mens Zero Rated} = 'yes', {Womens Zero Rated} = 'yes'), 0, 20)

As an aside, I removed the apostrophes from the gender values as you need to escape the apostrophes in the formulas.

JB

W
JonathanBowen
Forum|alt.badge.img+18

Hmmm, @Jeremy_Oglesby beat me to the punch :grinning_face_with_big_eyes: - nice consise answer too!

J
W
  • Will_Bailey
  • Author
  • New Participant
  • April 12, 2019

Oh wow! You guys are fantastic, thank you very much!

@Jeremy_Oglesby Using the SWITCH() function really helps to break it down and makes perfect sense. Interestingly the final 20 at the end resulted in an error and wouldn’t let me save the formula however after I removed that it works perfectly!

@JonathanBowen again, breaking it down into those steps really help to be able to see the steps of the formula

Thank you both of you for your quick and detailed responses, I think I’ll go ahead with using the SWITCH() function, purely due to the fact it reduces the number of columns in the table.

J
J
Forum|alt.badge.img+18

Oh wow! You guys are fantastic, thank you very much!

@Jeremy_Oglesby Using the SWITCH() function really helps to break it down and makes perfect sense. Interestingly the final 20 at the end resulted in an error and wouldn’t let me save the formula however after I removed that it works perfectly!

@JonathanBowen again, breaking it down into those steps really help to be able to see the steps of the formula

Thank you both of you for your quick and detailed responses, I think I’ll go ahead with using the SWITCH() function, purely due to the fact it reduces the number of columns in the table.


Turns out the error you got was because I forgot a comma after the “Women’s” IF() function – I edited my answer to include that comma. So if you decide you want that 20 as a default value, that’s how you can get it back in there

B
Terms & ConditionsAccessibility statement