Weighting ratings columns in formulas

Platform discussions Special workflows & use cases
1

Hi, I am using the Feature Tracking Board which uses a basic RICE Scoring formula ({Growth}{Reach}{Impact}*{Confidence})/{Effort} where each of the column names is a rating field.

Is there a way to equate the stars to different values?
For example, for
{Impact}, 1 star = .25, 2 stars = .5, 3 stars = 1, 4 stars = 2, 5 stars = 3
{Reach}, 1 star = 5, 2 stars = 10, 3 stars = 20, 4 stars = 30

ratings field is ideal, but if that is not possible, is there a different field type that could achieve the same result?

Thank you

2

@agotlieb

I think the simplest way would be to use the undocumented CHOOSE() function for each component, e.g.

CHOOSE({IMPACT}+1,0,0.25,0.5,1,2,5)

CHOOSE({REACH}+1,0,5,10,20,30,999)

Combining the components in your formula as something like:
CHOOSE({GROWTH}+1,0,1,2,3,4,5) * CHOOSE({REACH}+1,0,5,10,20,30,999) * CHOOSE({IMPACT}+1,0,0.25,0.5,1,2,5) * CHOOSE({CONFIDENCE}+1,0,1,2,3,4,5) / CHOOSE({EFFORT}+1,0,1,2,3,4,5)

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Should I convert my Integromat account to Make? - YouTube
Contact me directly here: Contact – The Monday Man

3 Likes
3

@JCorrell
Thank you, you got me on the right track, especially with syntax, but I ended up having to use SWITCH instead of CHOOSE (maybe because of my plan)

(SWITCH({Growth},1,1,2,2,3,3,4,5) * SWITCH({Reach},1,5,2,10,3,20,4,30) *
SWITCH({Revenue},1,1,2,5,3,10,4,20) *
SWITCH({Impact},1,0.25,2,0.50,3,1,4,2,5,3) * SWITCH({Confidence},1,0.10,2,0.50,3,0.80,4,1)) / SWITCH({Effort},1,1,2,2,3,3,4,5,5,8)

4

@agotlieb

SWITCH() works too, of course. It’s just a little more complex in this use case.

I am curious why you thought that you could not use CHOOSE(). If you have formulas, it should work.

Also, the final formula you gave will return an error if any of your rating columns have no stars selected and {Growth}, {Reach}, {Revenue}, or {Confidence} have 5 stars selected.

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Should I convert my Integromat account to Make? - YouTube
Contact me directly here: Contact – The Monday Man

5

@JCorrell
I just reset to Choose, which as you stated is a much cleaner solution. I missed the purpose of the 0 or 1 value at the beginning of the statement (CHOOSE({GROWTH}+1,0,…) which caused the invalid function error I was getting.

6

@agotlieb

Got it.

Thanks for circling back.

7

Hello,

Sorry to step in. I am interested in this choose function, basically for the same purpose as agotlieb. But I can’t find it in the list of available function in the formula …
Where did you find this function ? Where is it documented ?
Thanks for your answer
Franck

8

@FdL

CHOOSE() is among the list of (previously) undocumented functions available in monday formulas.

You can find my documentation about it and the other functions I found here: The monday Hidden Functions – The Monday Man

From the documentation:

CHOOSE
Returns the nth parameter from a list, returns an error when n does not map to a given parameter.
Syntax: CHOOSE(n, “val1” [,“val2”]…)
Example: CHOOSE(WEEKDAY(TODAY()), “sun”, “mon”, “tue”, “wed”, “thu”, “fri”, “sat”) => “mon” (in case where today is Monday)

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Should I convert my Integromat account to Make? - YouTube
Contact me directly here: Contact – The Monday Man

9

Awesome !
Thanks Jim for your answer and for providing all this information.
Have a great day

Franck

10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.