Need assistance with IF Formula/Turning Cells red or green

Tshelton

Hello everyone. I am trying to build a formula that will turn the values in the January - December columns red if they are less than the total in the Benchmark column or Green if they are higher. I have tried to do this at the cell level as well as by creating a column formula, but I keep getting errors. Can someone assist me with how to write this properly? I have included a screen shot below. Thank you in advance for your assistance!

Dale Murphy

@Tshelton If you don't mind, for my understanding why are the months in columns? What prevents you from running each month in its own row, and having each benchmark metric in separate columns?

(I believe such a layout can be used longer, and that it can help you solve your problem easier.)

dm

BullandKhmer

@Tshelton

Hi there,

Have you tried using the conditional formatting tool on the tool bar?

If you click on this and then add new rule....

Set Condition - (month) is less than baseline

Apply this format - (Red fill)

Apply to - (month) column.

Hope that helps , let me know if you need anything else.

Tshelton

This particular sheet was an import that was in use previously in Excel. You have a great point about flipping the rows/columns. I didn't even think about that. I will ask my teammates and verify if that is an option. In the event that we can not change them around, is it possible to change the cells red or green based on the number listed in the month columns based on the benchmark column amount?

@BullandKhmer I did try the conditional formatting option, but it was so time consuming to have to do each individual cell one at a time. I was finding that I had to enter the amount in each row of the benchmark field for each of the months in the row. It also didn't allow me to keep the formatting if the Benchmark number changed. I am very new to smartsheet, so this could all just be newbie error, but I couldn't seem to find a quick way to set up the conditional formatting that let me go row by row.

Thank you both for your help!

BullandKhmer

@Tshelton if you highlight all the columns (not a single cell) prior to using the conditional formatting tool then the conditional formatting will apply to all selected columns. This should only take a few minutes.

I'm not busy, Ill walk you though it on a zoom if you want.

adam@bullandkhmer.com.

Tshelton

@BullandKhmer I will give that a shot. Thank you for your help!