override value for '0'

tejaswini
tejaswini
in Modeling

Hi 

 

I have 3 Line items 

1st -expense(number format)

2nd-Override Value(number format)

3rd-Final Expense(number Format)

formula for final expense is IF OVERRIDE  VALUE > 0 THEN OVERRIDE VALUE ELSE EXPENSE

So if want to override expense value with 0 then how can I modify the formula for final expense with out using any extra line item for the user to click 

Thanks 

Tejaswini

Tagged:

Best Answer

  • jackcplanning
    jackcplanning
    Answer ✓

    Hi @tejaswini

    An alternative (though probably not a great solution) is to set the override as a text-formatted line item.

    When a user enters 0 into that line item, you can evaluate it in the 3rd line item with VALUE(x) to determine if there's a number there.

    Formula : IF ISBLANK(override) THEN initial value ELSE VALUE(override)

    Override 0.PNG

    This does not prevent users from entering non-numeric values, which will evaluate to NaN in the final value. This may be preferable if you're trying to alert the end user that it's invalid.

    However, if you want to default to the initial value, you can add an additional evaluation IF ABS(VALUE(override)) >= 0 THEN... This will check to see if the override evaluates to a number, and if it doesn't, default to initial value.

Answers

  • Noemi.andres
    Noemi.andres

    Hi @tejaswini 

     

    You will need an additional line item formatted as boolean. Then you can amend the formula in the final expenses line item so that when the user ticks the box in the new boolean line item the final expenses value is overriden to zero.

     

     

  • Michelle Sutherland
    Michelle Sutherland

    Yes agree, the only way is to have the Boolean to say you want to use the Override number.  Also using > 0 would not allow an override with a negative number either so a Boolean is always best for Overrides.

Categories