IF AND "unparseable" - desparation
Hi,
If have no other ideas any more - maybe some fresh eyes could help me? There's something in there it doesn't like
Thanks a lot
Katja
- IF([1. Tax Class]@row = "Duty paid", "very low"
- If 1. Tax Class@row = “bonded” and 3. Volume flag@row = “no”, then “low risk”
- If 1. TaxClass@row = “bonded” und 3. Volume flag@row = “yes”, then “medium risk”
- Otherwise “high risk”
=IF(1. Tax Class@row = “Duty paid”, “very low”, AND(1. Tax Class@row = ”bonded”, [3. Volume flag]@row = “no”), “low risk”, IF(AND(1. Tax Class@row = “bonded”, [3. Volume flag}@row = “yes”), “medium risk”, “high risk”))
Best Answer
-
- You need square brackets around every column name that has spaces in (or ends in a number). You are missing these on 1. Tax Class and have only the opening one for 3. Volume flag (the curly bracket should not be there).
- You also need to use straight quotation marks not the curved smart quotes. You should be OK if you type these in or paste them into a text editor (like notepad) and copy from there (not word).
- You are also missing the second IF function (you have the AND, but need the IF as you have for the 3rd part). When you add the IF you will be adding an opening parenthesis, so also need another closing one at the end.
I have highlighted all the changes in bold.
=IF([1. Tax Class]@row = "Duty paid", "very low", IF(AND([1. Tax Class]@row = "bonded", [3. Volume flag]@row = "no"), "low risk", IF(AND([1. Tax Class]@row = "bonded", [3. Volume flag]@row = "yes"), "medium risk", "high risk")))
Hope this helps.
Answers
-
- You need square brackets around every column name that has spaces in (or ends in a number). You are missing these on 1. Tax Class and have only the opening one for 3. Volume flag (the curly bracket should not be there).
- You also need to use straight quotation marks not the curved smart quotes. You should be OK if you type these in or paste them into a text editor (like notepad) and copy from there (not word).
- You are also missing the second IF function (you have the AND, but need the IF as you have for the 3rd part). When you add the IF you will be adding an opening parenthesis, so also need another closing one at the end.
I have highlighted all the changes in bold.
=IF([1. Tax Class]@row = "Duty paid", "very low", IF(AND([1. Tax Class]@row = "bonded", [3. Volume flag]@row = "no"), "low risk", IF(AND([1. Tax Class]@row = "bonded", [3. Volume flag]@row = "yes"), "medium risk", "high risk")))
Hope this helps.
-
Post a picture of your sheet for better understanding of the fields. These are some thoughts.
- Are you saying that 1. Tax Class can contain both Duty Paid and Bonded in the same cell?
- Do you have a 2. Tax Class column? Should there be one?
- I would put Duty Paid and Bonded in two different columns.
- If Duty Paid and Bonded are in same column, AND will not work.
-
Hey KPH,
this was very helpful and it brought me one step ahead. Thank you so much…tho formula itself seems to work in its basic = "duty paid" = "very low" but in the rows, where it is asked to check "bonded" - "yes" / "no", it's saying #INVALID Data Type"….I checked all the spelling, all columns are "Text/numbers" field.
Any experience regarding this?
Happy Monday
-
meant to add the latest version:
=IF([1. Tax Class]@row = "Duty paid", "very low", AND(IF([1. Tax Class]@row = "Bonded", [3. Volume flag]@row = "yes"), "medium risk", AND(IF([1. Tax Class]@row = "Bonded", [3. Volume flag]@row = "no"), "low", "high")))
-
I have it!!!! celebration - I mixed up the order "AND(IF) instead of "IF(AND)
-
you made me happy and the HMRC as well - I promised and automated database for these checks
-
Great news @KatjaCompliance . I'm glad that worked for you. Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!