Multiple If/IsNA formula issue

[Beattie101]

Hi Everyone,

I have an issue and cant work around it.

In the attached workbook I'm trying to achieve the following:
Column F needs to be a "4" if column B is over "90" if not then 7. Now this needs to include Column A and it needs to contain NZ. If Column "B" is NA then it needs to be 7.

Column F needs to be a "4" if column B is over "90" if not then 8. Now this needs to include Column A and it needs to contain AU. If Column "B" is NA then it needs to be 8.

Basically I have 2 different countries with 2 different scoring methods. Column B and Column C is normally a Vlookup on my agents Name from a different spreadsheet.

I can get the AU and NZ part but cannot work out how to add the "isna' in there. When I add it in 2 different areas it only looks at the first isna and puts a 7. But I need it to be 8 for AU.


Hope someone can help

[FDibbins]

See if this will work for you?
=IF(ISERROR(B2),IF(A2="NZ",7,8),4)

[AliGW]

Welcome to the forum.

Try this:

=IF(AND(OR(A2="NZ",A2="AU"),B2>=90),4,IF(AND(A2="NZ",B2<90),7,IF(AND(A2="AU",B2<90),8,"N/A")))

[Beattie101]

FDibbins - Your truley amazing. I think my mind was too deeply involved with this to not think of a completely different forumula. Something so simple has worked a treat. Thanks heaps.

[FDibbins]

Happy to help and thanks for the feedback