Sep 18, 2021 09:26 AM
{Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement} / {Story Point Estimate}
The first part works great then I add the / and error
its nothing to do with the format of the fields as {Story Point Estimate} + {Business User Value} works fine… any ideas? its seem really odd to me
Solved! Go to Solution.
Sep 20, 2021 01:09 PM
For future-proofing and ensuring proper functionality in all cases, I’d recommend wrapping ALL of your field names that reference Single-Select fields with which you want to do math operations in the VALUE()
function (including the other field names in the formula you just posted), to make the conversion from string to integer explicit. 6 months from now when something breaks and you go back to look at this and try to figure out what the heck is going on, you’ll be glad to have your formulas be as explicit as possible – don’t trust yourself to remember that all the other values are being implicitly converted, while the one divisor is being explicitly converted.
Sep 18, 2021 10:20 AM
What’s the error you’re getting?
Did you try wrapping the numerator in parentheses?
Sep 18, 2021 10:47 PM
Are you dividing by zero?
Sep 20, 2021 10:27 AM
Nope story point numbers which are selectable items the odd thing is that I can add them and - them away I can / them? it look like a bug to me personally
Sep 20, 2021 10:36 AM
Can you post a screenshot showing all relevant fields/sample values?
Sep 20, 2021 10:55 AM
And the error. @Jeremy_Oglesby asked about that above, and that might also provide clues.
Sep 20, 2021 12:32 PM
This is why I think its a real bug this works {Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement} + {Story Point Estimate}
This however does not
{Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement} / {Story Point Estimate}
And Neither does
({Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement}) / {Story Point Estimate}
And Tried
({Business User Value} + {Time Criticality} +{Risk Reduction - Opportunity Enablement}) / ({Story Point Estimate})
Just in case also still #ERROR!
Sep 20, 2021 12:34 PM
Yep tired that… The Error is #ERROR!
Sep 20, 2021 12:37 PM
Sounds like it could be related to the field type of one of your fields. Is one of your fields in the formula a Lookup or other non-number field?
Sep 20, 2021 12:39 PM
Nope and if that was the case why would Adding them all work but dividing does not? They are all single select numbers FYI
This works
{Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement} + {Story Point Estimate}
Sep 20, 2021 12:44 PM
This creates and Error
Sep 20, 2021 12:44 PM
As you pointed out, a bug… could be a bug where the “+” operator gracefully handles lookup values, but the “/“ operator does not.
However, if all of your fields are just straight number fields, and there’s no possibility that any of them could be an array of values or a string value behind the scenes, then I don’t think I can provide any further input, as I’ve never encountered an issue like this myself.
Sep 20, 2021 12:46 PM
This works…
Sep 20, 2021 12:47 PM
Ah, those single-select fields are all technically strings values.
Try wrapping each reference to a single-select field in the VALUE()
function to convert it to an integer.
Sep 20, 2021 12:47 PM
So would you agree its a BUG then? do we know how I contact the developers to get this fixed? if that possible?
Sep 20, 2021 12:48 PM
Tried that already that does not work?
Sep 20, 2021 12:50 PM
Which field is the {Story Point Estimate}
field? Is it the number field (which is blank), or the single-select field with “55” in it?
Sep 20, 2021 12:52 PM
one of the values has 55 the others are black right now because I am not going to populate any more until it works… but the point it works with a + and - but not with a * or a / surely is the issue here?
Sep 20, 2021 12:56 PM
All working formula looks like this.
({Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement}) + {Story Point Estimate}
Sep 20, 2021 12:58 PM
No, this definitely has to do w/ the string values. If I wrap them all in VALUE()
the division works fine for me:
There may be a bug here in the sense that the "+"
operator seems to be happy to gracefully convert your string into a number for you, while the "/"
operator will not, but this can be worked around by manually converting all the string values to numbers using the VALUE()
function.