IF formula correction please

EliseB

Hi There,

It is a Monday morning for me and a distinct lack of coffee means that the above formula is likely an absolute shemozzle. Essentially I am trying to return an argument that says If the Priority rating shows this (eg H5) then return this number (eg 15) in the helper column.

This formula (once correct!) then also need to be entered into a helper column for "Size of Audience & Impact" which also has these values, so I can then use both helper columns for a scatter graph - to create a matrix of sort for priority vs impact.

Saying all of this hurts my head already, please help :)

Thanks

Elise

Kelly Moore

Hey

The Left is what grabs the letter to evaluate. An Unparseable is typically a missing parenthesis, bracket or comma. My formula was working so let me double check what I copied it into the post. This works below

=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))

Kelly Moore

If your numbers in the Priority rating are always single digits (I wasn't sure they would be so the original formula accounts for that) then you can use this

=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(RIGHT([Priority Rating]@row, 1)) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(RIGHT([Priority Rating]@row, 1)) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(RIGHT([Priority Rating]@row, 1)))))

Kelly Moore

Hey @EliseB

Your nested IF approach is a valid approach however it becomes difficult to maintain if your values change or additional responses are added. I tried to provide an approach that was a bit more dynamic. There are other ways this could be done.

This approach checks first to see what the first character is, then adds the appropriate amount depending on that c character. The SUBSTITUTE function produces a text string and the VALUE function converts the text string back into an actual number.

=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT[Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))

Will this work for you?

Kelly

EliseB

Hi Kelly,

Thank you but no unfortunately that doesn't work for me, it comes up as #Unparseable, I assume that is because the values are H5, H4, H3 etc rather than H alone? I don't understand the need for the LEFT when referencing the Priority rating column either?

I should add that there will be no additional options being entered however there may be an instance where a high priority item moves from say H5 to H4 which is the reason I wanted to write a formula rather than just enter the corresponding numbers below in the fields

Kelly Moore

Hey

The Left is what grabs the letter to evaluate. An Unparseable is typically a missing parenthesis, bracket or comma. My formula was working so let me double check what I copied it into the post. This works below

=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(SUBSTITUTE([Priority Rating]@row, LEFT([Priority Rating]@row, 1), "")))))

Kelly Moore

If your numbers in the Priority rating are always single digits (I wasn't sure they would be so the original formula accounts for that) then you can use this

=IF(LEFT([Priority Rating]@row, 1) = "H", VALUE(RIGHT([Priority Rating]@row, 1)) + 10, IF(LEFT([Priority Rating]@row, 1) = "M", VALUE(RIGHT([Priority Rating]@row, 1)) + 5, IF(LEFT([Priority Rating]@row, 1) = "L", VALUE(RIGHT([Priority Rating]@row, 1)))))

EliseB

Thank you so much Kelly, I had no idea about the left condition, that's a game changer! I tried both the middle and the bottom option and they both work perfectly! Thank you that has saved me hours of trouble and anxiety.

Really appreciate it,

Elise