Skip to main content
Question

If formula that can display value from one of two fields

  • July 3, 2019
  • 0 replies
  • 68 views
I need advice on composing a formula for a numeric field. 

Basically, I have 3 numeric fields: an "estimated" value (Field A), an "actual" value (Field B) and a "Final" value (Field C). I want to display my "actual" value (Field B) in Field C unless the "actual" value (Field B) is blank then I want to display the "estimated" value (Field A) in Field C.

I've tried: If(isnull([Actual]), [Estimated], [Actual]) but it just keeps returned a value of "0". 
    This topic has been closed for replies.

    Try swapping out isnull() with a Nz() function. IsNull can have some weird behavior with 0 versus actual nulls. Nz() will force the value to read as 0 and you can compare to that.

    So like:

    If(Nz([Actual])=0, [Estimated], [Actual])

    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
    • JordanLyon
    • Author
    • Registered
    • July 3, 2019
    That almost works, except, sometimes my "Actual" can be "0". In which case, I would want Field C to display "0". With the formula you provided, Field C is displaying "Estimated" when "Actual" is "0". 

    Is there a way around this?
    AdamKeever1
    Forum|alt.badge.img+2
    • AdamKeever1
    • Registered
    • July 3, 2019
    Jordan, your first formula works for me, but you need to first go to the properties for your "Actual" field an uncheck the box for 'Treat blank values as "0" in calculations'.


    Terms & ConditionsAccessibility statement