Translate Text fields to Numeric values to make calculations

Now, since we are able to calculate things :slight_smile: it would be nice to explore more use cases.

As a product owner I would like to create a field that calculates what feature we should be developing next to create the most value in the time we have. I would like to do this based on a given label (MOSCOW) and points (SCRUM).

The formula looks something like this:
[Weight factor based on MOSCOW] / [Estimated work (Points)] = Prioritization Score
Tasks with the highest score should be done first, because these deliver the most value per point.

Now there are 2 challenges here:

  1. The Moscow labels should translate to a value to be able to make a calculation with it
  2. The Fibonacci scheme in a dropdown should translate to a value to be able to make a calculation with it. See also:
    Custom Fields - Detect dropdown values as numeric values in calculations

Now, I see three solutions here:

  1. Give numeric values to Option names in the field editor
  2. IF statements in Formula’s so we can map text values to numeric values.
    This would open probably much more possibilities I think.
  3. A rule that puts a value in another field based on a value in a field.

We would love this as well, for similar reasons! I head the Education team for a SaaS company and we get way more requests than we can do, so we’re developing an “impact score” based on market vertical, type of request/update, users impacted - about five metrics overall. I would love to have that automatically calculate rather than having to use a separate spreadsheet.

Hi @Thomas_van_der_Woude

I believe your option 3 is possible right now using Rules to convert dropdown values to numbers. And then you can insert those number fields into formulas to get a result.

Pinging @Arthur_BEGOU who built a fantastic quiz for us last year, and I’m certain this is what he did in the back-end to generate the scores :wink:

1 Like

Thanks @Richard_Sather :rocket:

Yesss :wink:
I’m explaining the process in details in that other thread : Using Forms as a risk assessment - #2 by Arthur_BEGOU


Brilliant, thanks!

1 Like