Help with an IF statement

Kegan

Hi guys,

 

I'm in need of some help with regards to trying to use an IF statement to manage Priority rankings.

 

Basically, i have two columns that rank boht Criticality and Manageability, dependant on the score obtained across boht of these dictates the Priority ranking applied to a Risk.

 

I'm currently receiving an #INVALID DATA TYPE error when running this formula...any help would be much appreciated.

 

=IF([Criticality Score]2 * [Manageable Score]2 > 12, "1 - Critical", IF([Criticality Score]2 * [Manageable Score]2 > 6, "2 - High", IF([Criticality Score]2 * [Manageable Score]2 > 2, "3 - Medium", "4 - Low")))

 

Regards

Kegan

 

Tim Meeks

Kegan,

 

I'm not the best formula person in the SS community.. there are some guys/gals who are EXPERTS who could do this in their sleep.  

 

However, I replicated your data and formula and it worked for me. (see the images.)

Named the columns the same and didn't have to change anything on your formula. Just pasted it in and it worked.  

How is your original score being calculated? Is it raw data entry or is it a dropdown?

Regardless, I got it to work with a drop down and just keying in the data.

 

Can you provide more details?

 

thanks,

Kegan

Hi Tim,

 

Firstly, thanks very much for offering to help me out on this! 

 

As per the screenshot below i have set up the additional columns as -

1. 1. Both the 'Critical-ity' and 'Manageable?' columns working off a dropdown filter.

2. Criticality Score formula is: 

=IF([Critical-ity]1 = "Low", "1", IF([Critical-ity]1 = "Below Average", "2", IF([Critical-ity]1 = "Above Average", "3", IF([Critical-ity]1 = "High", "4"))))

3. Manageable Score forumla is:

=IF(Manageable?1 = "Yes", "1", IF(Manageable?1 = "Probably", "2", IF(Manageable?1 = "Maybe", "3", IF(Manageable?1 = "No", "4"))))

 

I actually now wondering if the issue is because my 'Priority' column is an IF formula which in turn is referencing two other columns ('Criticality Score' & 'Manageable Score') that both contain their own respective IF formulas?

 

Food for thought.....  I'm stuck on this one!  Thanks again for you help.

 

Kegan

Travis

Kegan, you are getting the error because the formulas located in your Criticality Score and Manageable Score columns are producing text results - not numerical. In formulas when you add quotes around something, it is formatted as a text string (even if it is a number). 

 

There are two options for fixing this...

 

1. Remove the quotes around the "value_if_true" numbers.

 

Example:

Original: =IF([Critical-ity]1 = "Low", "1", IF([Critical-ity]1 = "Below Average", "2", IF([Critical-ity]1 = "Above Average", "3", IF([Critical-ity]1 = "High", "4"))))

 

New: =IF([Critical-ity]1 = "Low", 1, IF([Critical-ity]1 = "Below Average", 2, IF([Critical-ity]1 = "Above Average", 3, IF([Critical-ity]1 = "High", 4))))

 

2. Define referenced cells as numbers

 

Example:

Origonal:  =IF([Criticality Score]2 * [Manageable Score]2 > 12, "1 - Critical", IF([Criticality Score]2 * [Manageable Score]2 > 6, "2 - High", IF([Criticality Score]2 * [Manageable Score]2 > 2, "3 - Medium", "4 - Low")))

 

New: =IF(VALUE([Criticality Score]2) * VALUE([Manageable Score]2) > 12, "1 - Critical", IF(VALUE([Criticality Score]2) * VALUE([Manageable Score]2) > 6, "2 - High", IF(VALUE([Criticality Score]2) * VALUE([Manageable Score]2) > 2, "3 - Medium", "4 - Low")))

 

The first option is likely what you will want to do. I just wanted to show the second option as an FYI for future formula building