Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Formula not working / just creates an error

Topic Labels: Formulas
Solved
Jump to Solution
621 28
cancel
Showing results for 
Search instead for 
Did you mean: 

{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

1 Solution

Accepted Solutions

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.

See Solution in Thread

28 Replies 28

What’s the error you’re getting?

Did you try wrapping the numerator in parentheses?

Are 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

Can you post a screenshot showing all relevant fields/sample values?

And the error. @Jeremy_Oglesby asked about that above, and that might also provide clues.

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!

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?

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}

image

This creates and Error

image

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.

This works…

image

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.

So would you agree its a BUG then? do we know how I contact the developers to get this fixed? if that possible?

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?

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?

image

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:
CleanShot 2021-09-20 at 12.57.52
CleanShot 2021-09-20 at 12.58.46

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.