Formula for parent arrow to change based on the average child arrows

Maida Omerovic

Hi all -

I need help with formula to average the arrows and show the appropriate arrow based on that average, i.e. if majority of arrows are down, then the arrow in the orange section would be red, etc. Hope this makes sense.

Michael Culley

Actually I just realized you want essentially the one that shows up most often (aka the MODE). Smartsheet doesn't have a mode function but there's a workaround here:

https://community.smartsheet.com/discussion/42701/formula-to-retrieve-most-frequent-number

Michael Culley

I wonder if it might help to create a helper column where you assign a number for each arrow.

For example:

=IF([RAID Level]@row = "Down", 1, IF([RAID Level]@row = "Angle Down", 2, IF([RAID Level]@row = "Sideways", 3, IF([RAID Level]@row = "Angle Up", 4, IF([RAID Level]@row = "Up", 5, 0)))))

Then you can average those numbers and if the average = 1, "Down", if average = 2, "Arrow Down"

etc. etc.

What do you think about that?

Michael Culley

Actually I just realized you want essentially the one that shows up most often (aka the MODE). Smartsheet doesn't have a mode function but there's a workaround here:

https://community.smartsheet.com/discussion/42701/formula-to-retrieve-most-frequent-number

Maida Omerovic

Thank you, Michael!