Formula Help w/arrows

Maida Omerovic

Hello,

I need help with the following formula:

=IF([Risk Level Total (IMPACTxPROBABILITY)]@row = 25, "Down", IF([Risk Level Total (IMPACTxPROBABILITY)]@row <= 20, "Angle Down", IF([Risk Level Total (IMPACTxPROBABILITY)]@row <= 12, "Sideways", IF([Risk Level Total (IMPACTxPROBABILITY)]@row <= 10, "Angle Up", "Up"))))

I need "Down" if score is 25, "Angle Down" if score is between 15 and 20, "Sideways" if score is between 10-12, "Angle Up" is score is between 5-9, and Up if score is between 1-4. Thank you!

John Jonassen

Try this. Keeping in mind that there are some scores that won't equate to a symbol. Example, 21 through 24 will not.

=IF([Risk Level Total (IMPACTxPROBABILITY)]@row = 25, "Down", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row >= 15, [Risk Level Total (IMPACTxPROBABILITY)]@row <= 20), "Angle Down", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row >= 10, [Risk Level Total (IMPACTxPROBABILITY)]@row <= 12), "Sideways", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row >= 5, [Risk Level Total (IMPACTxPROBABILITY)]@row <= 9), "Angle Up", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row >= 1, [Risk Level Total (IMPACTxPROBABILITY)]@row <= 4), "Up", "")))))

Maida Omerovic

This is it. Thank you so much!

David Joyeuse

Hi @Maida Omerovic

There's many ways to do your nested IF but let's ride with this one, and I'd like to point out a few points first:

• What happens if score is between 21 and 24?
• What happens if score is either 13 or 14?
• What happens if score is 0?

Those two cases are not part of what you want to do?

Maybe your score cannot reach these numbers, in which case it's fine, but if they do, the formula will return the default answer. ("Up" in your case).

Keeping this in mind, here we go so that we cover every possibilities as you want them:

=IF([Risk Level Total (IMPACTxPROBABILITY)]@row>5, IF([Risk Level Total (IMPACTxPROBABILITY)]@row>10,IF([Risk Level Total (IMPACTxPROBABILITY)]@row>15, IF([Risk Level Total (IMPACTxPROBABILITY)]@row>20, IF([Risk Level Total (IMPACTxPROBABILITY)]@row=25, "Down", ""),"Angle Down"),IF([Risk Level Total (IMPACTxPROBABILITY)]@row>12, "", "Sideways),"Angle Up"), IF([Risk Level Total (IMPACTxPROBABILITY)]@row=0, "", "Up")

Other way to do it using AND:

=IF([Risk Level Total (IMPACTxPROBABILITY)]@row=25, "Down", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row>=15, [Risk Level Total (IMPACTxPROBABILITY)]@row=<20, "Angle Down", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row>=10, [Risk Level Total (IMPACTxPROBABILITY)]@row=<12), "Sideways", IF(AND([Risk Level Total (IMPACTxPROBABILITY)]@row>=5, [Risk Level Total (IMPACTxPROBABILITY)]@row=<9), "Angle Up", IF([Risk Level Total (IMPACTxPROBABILITY)]@row>=1,[Risk Level Total (IMPACTxPROBABILITY)]@row=<4, "Up", "")))))

Up to you!

Hope it helped!