New to Formulas - Need help with Text Answers

Mytch Jeffrey

I have tried to create a formula using three options being
1.  Does not meet Insurance Standards,

2. Meet Insurance Standards

3. Exceeds Insurance Standards

 

for a particular series of questions.

 

If you choose "Does not Meet Insurance Standards" then then next column Score will also populate 1 and the cell will highlight yellow.  If you enter  "Meets Insurance Standards" then the Score  will autopopulate 2 and the cell will highlight green.  If you enter "Exceeds Insurance Standards" the Score cell will auto populate 3 and the cell will highlight darker green.

 

Please help someone

 

Mytch

Mytch Jeffrey

Sorry I should have said that the Score cell will be coloured in relation to the numeric value ie 1 = Yellow, 2 = Light Green and 3 = Darker Green

 

Hope this makes sense

 

Mytch

J. Craig Williams

Mytch,

 

A couple different ways to do this, depending on what you need and like.

Let's start with the easy part:

 

Once you have that number in the Score column, go to the Conditional Formatting icon on the tool bar

 

 

and add three rules there.

http://help.smartsheet.com/customer/portal/articles/516359-conditional-formatting

 

You could use either the Score or Standards column as your criteria.

But let's assume that the conditional formatting is not the source of your problem.

 

A few questions I would ask:

1. Are you using the Score for anything other than color?

If not, you can have the conditional formatting directly on the Standards column.

I suspect it is used in weighting of some fashion - but wanted to point that out - and provide answers.

 

2. Are you OK with the number being in the Standard selection?
That is, 

 

1.  Does not meet Insurance Standards

2. Meet Insurance Standards

3. Exceeds Insurance Standards

 

vs this

 

Does not meet Insurance Standards

Meet Insurance Standards

Exceeds Insurance Standards

 

as the options.

The formula for that would be:

 

=VALUE(LEFT([Standards]23, 1))

 

for row 23

- this just takes the first character and turns it into a number

(again, I'm assuming the number goes into a calc somewhere.)

 

Assuming you like the other (non-number options) then

 

=IF(Standards23 = "Does not meet Insurance Standards", 1, IF(Standards23 = "Meet Insurance Standards", 2, IF(Standards23 = "Exceeds Insurance Standards", 3, 0)))

 

for row 23

works, but is kind of ugly because the text is so long.

Since the three texts start with different letters, though ....

 

=IF(LEFT(Standards23, 1) = "D", 1, IF(LEFT(Standards23, 1) = "M", 2, IF(LEFT(Standards23, 1) = "E", 3, 0)))

 

for row 23

The full text is probably easier to read.

 

If the choices are more varied, then I might be tempted to use the FIND() function

but one must be careful as "Meet" shows up in "Meet" and "Does not meet" (Find is not case-senstitive)

 

I hope this helps.

 

Craig

 

 

 

 

 

 

 

Mytch Jeffrey

Thanks Craig for you advice.  It will give it a try.

 

Mytch