Forum Discussion

JordanLyon's avatar
JordanLyon
Qrew Trainee
6 years ago

If formula that can display value from one of two fields

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". 
Formulas and functions
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    6 years ago
    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's avatar
    JordanLyon
    Qrew Trainee
    6 years ago
    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's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    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'.