IF AND "unparseable" - desparation

KatjaCompliance

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

1. IF([1. Tax Class]@row = "Duty paid", "very low"
2. If 1. Tax Class@row = “bonded” and 3. Volume flag@row = “no”, then “low risk”
3. If 1. TaxClass@row = “bonded” und 3. Volume flag@row = “yes”, then “medium risk”
4. 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”))

KPH

1. 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).
2. 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).
3. 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.

KPH

1. 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).
2. 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).
3. 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.

dojones

Post a picture of your sheet for better understanding of the fields. These are some thoughts.

1. Are you saying that 1. Tax Class can contain both Duty Paid and Bonded in the same cell?
2. Do you have a 2. Tax Class column? Should there be one?
3. I would put Duty Paid and Bonded in two different columns.
4. If Duty Paid and Bonded are in same column, AND will not work.

KatjaCompliance

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

KatjaCompliance

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")))

KatjaCompliance

I have it!!!! celebration - I mixed up the order "AND(IF) instead of "IF(AND)

KatjaCompliance

you made me happy and the HMRC as well - I promised and automated database for these checks

KPH

Great news @KatjaCompliance . I'm glad that worked for you. Happy to help.