Formula Help with Symbols Column

Lisa

Hey, can anyone help me with this? I have a column called Time to Comp which is a dropdown with the following -

5

10

15

30

45

1hr

2hr

4hr

8hr

I have another column called TimeCompSym with the 1 - 5 stars. I would like this column to populate the stars based on the value in the Time to Comp column.

1 star = 5

2 stars = 10 or 15

3 stars = 30

4 stars = 45

5 stars = 1hr or 2hr or 4hr or 8hr

I am not great at nested if statements so if help would be greatly appreciated, thank you!!!!

Lisa

 

Paul Newcome

=IF(ISBLANK([Time to Comp]@row), "Empty", IF([Time To Comp]@row = "5", "One", IF(OR([Time To Comp]@row = "10", [Time To Comp]@row = "15"), "Two", IF([Time To Comp]@row = "30", "Three", IF([Time To Comp]@row = "45", "Four", IF(OR([Time To Comp]@row = "1hr", [Time To Comp]@row = "2hr", [Time To Comp]@row = "4hr", [Time To Comp]@row = "8hr"), "Five"))))))

 

Give this a try.

Lisa

It worked perfectly! I never saw the use of @row before, I thought I would have to put in the row number after I pasted it in but it works fine without it. 

Thank you so so much!!!!

Paul Newcome

Happy to help. I use @row a lot. It makes copy/pasting so much easier.

Richard Rymill SBP

Nice solution there Paul. 

Thanks for sharing! 

 

RichardR

SMarter Business processes.