+4{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
Best answer by Jeremy_Oglesby
@Jeremy_Oglesby THANKYOU odd if I put VALUE on everything it did not work but if I do it just one the / value like this finally works so very inconsistent behaviours going on but very grateful for a working scrore
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.
+25Are you dividing by zero?
+4Are you dividing by zero?
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
+25Nope 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
Can you post a screenshot showing all relevant fields/sample values?
+4This 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!
+4What’s the error you’re getting?
Did you try wrapping the numerator in parentheses?
Yep tired that… The Error is #ERROR!
Yep tired that… The Error is #ERROR!
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?
+4Sounds 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?
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}
+4Can you post a screenshot showing all relevant fields/sample values?
This creates and Error
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}
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.
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}
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.
+4As 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.
So would you agree its a BUG then? do we know how I contact the developers to get this fixed? if that possible?
+4Ah, 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.
Tried that already that does not work?
Tried that already that does not work?
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?
+4Which field is the {Story Point Estimate} field? Is it the number field (which is blank), or the single-select field with “55” in it?
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?
+4
All working formula looks like this.
({Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement}) + {Story Point Estimate}
All working formula looks like this.
({Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement}) + {Story Point Estimate}
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.
+4
Formula now creates an #ERROR! only difference / rather then +
({Business User Value} + {Time Criticality} + {Risk Reduction - Opportunity Enablement}) / {Story Point Estimate}
+4No, 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.
Try naming your fields with words with words… I have dont that with mine and it does not work I will do again now and send over for you to see
Try naming your fields with words with words… I have dont that with mine and it does not work I will do again now and send over for you to see
+4@Jeremy_Oglesby THANKYOU odd if I put VALUE on everything it did not work but if I do it just one the / value like this finally works so very inconsistent behaviours going on but very grateful for a working scrore
@Jeremy_Oglesby THANKYOU odd if I put VALUE on everything it did not work but if I do it just one the / value like this finally works so very inconsistent behaviours going on but very grateful for a working scrore
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.
+25@Jeremy_Oglesby THANKYOU odd if I put VALUE on everything it did not work but if I do it just one the / value like this finally works so very inconsistent behaviours going on but very grateful for a working scrore
Just to reiterate, this is not a bug. You are adding/dividing several strings that look like numbers, but are not actually numbers. This is why you need to use VALUE(). Math operators are intended to work for numbers and not strings, issues that arise from feeding a math-based formula strings instead of numbers are to be expected.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
