Multi-Parameter Color Ball Formula

Adam Kinney

How can I have the colored balls in the Last Activity Status column change colors automatically based on the stage it is in and number of days since last activity?

Meaning the individual stages in the Last Activity Type column can be Blank, Initial Presentation, Survey, Final, Verbal Agreement, Qualified Win.

If the Last Activity Type is Initial Presentation I want the formula to indicate: 10 or less Green, 11-18 Yellow, over 18 Red.

If Last Activity Date is Survey I want the formula to indicate: 7 or less green, 8-14 yellow, 15 or greater Red

If the Last Activity Date is Final Presentation I want the formula to indicate: 14 or less green, 15-20 yellow, 20 or greater Red.

If Verbal Agreement or Qualified Win Green.

If Blank Gray.

Sameer Karkhanis

1. You need to have the column type set to visual symbols, which you already have.
2. Write a nested IF formula using the conditions you have mentioned and then convert the column to Formula Column (you can do by right clicking on the cell you defined the formula and selecting from the context menu).

Something like this (I have written the formula based on "Stage Progress" column but you can replace with whatever column name you want. Your days since last activity is way too off from your criteria so did not use that)

--- Start of formula ---

IF([Last Activity Type]@row = "Initial Presentation", IF([Stage Progress]@row <= 10, "Green", IF([Stage Progress]@row > 18, "Red", "Yellow")),

IF([Last Activity Type]@row = "Survey", IF([Stage Progress]@row <= 7, "Green", IF([Stage Progress]@row > 14, "Red", "Yellow")),

IF([Last Activity Type]@row = "Final Presentation", IF([Stage Progress]@row <= 14, "Green", IF([Stage Progress]@row > 19, "Red", "Yellow")),

IF(OR([Last Activity Type]@row = "Verbal Agreement", [Last Activity Type]@row = "Qualified Win"), "Green", "Gray")  )))

-- End of formula ---

Sameer Karkhanis

1. You need to have the column type set to visual symbols, which you already have.
2. Write a nested IF formula using the conditions you have mentioned and then convert the column to Formula Column (you can do by right clicking on the cell you defined the formula and selecting from the context menu).

Something like this (I have written the formula based on "Stage Progress" column but you can replace with whatever column name you want. Your days since last activity is way too off from your criteria so did not use that)

--- Start of formula ---

IF([Last Activity Type]@row = "Initial Presentation", IF([Stage Progress]@row <= 10, "Green", IF([Stage Progress]@row > 18, "Red", "Yellow")),

IF([Last Activity Type]@row = "Survey", IF([Stage Progress]@row <= 7, "Green", IF([Stage Progress]@row > 14, "Red", "Yellow")),

IF([Last Activity Type]@row = "Final Presentation", IF([Stage Progress]@row <= 14, "Green", IF([Stage Progress]@row > 19, "Red", "Yellow")),

IF(OR([Last Activity Type]@row = "Verbal Agreement", [Last Activity Type]@row = "Qualified Win"), "Green", "Gray")  )))

-- End of formula ---