Forum Discussion

CHaines's avatar
CHaines
Advocate I
3 years ago
Solved

Find difference only if 2 columns are present, else blank

I expect only to get a result only if both are present. If either is blank, or both are blank, I expect a blank result. Sample data showing each case. Blank cells are intentionally blank, and RoutePriceChange is the expected output value.

 

AllChanges table:

FirstPlanValueLastPlanValueRoutePriceChange
12  
 18 
1587
   

 

So far I have tried 

 

 

 

RoutePriceChange = AllChanges[LastPlanRouteValue] - AllChanges[FirstPlanRouteValue]

 

and

 

RoutePriceChange = 
IF( OR(ISBLANK(AllChanges[LastPlanRouteValue]), ISBLANK(AllChanges[FirstPlanRouteValue])),
    BLANK(),
    AllChanges[LastPlanRouteValue] - AllChanges[FirstPlanRouteValue]
    )

 

but both sample codes provide a value when FirstPlanRouteValue is present AND LastPlanRouteValue is missing.

Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    Hey CHaines,

    Do your columns have the data type 'Whole Number' or 'Decimal Number'? If so, your own calculation should work. Anyway, it works for me:

4 Replies

  • Barthel's avatar
    Barthel
    Solution Sage
    3 years ago

    Hey CHaines,

    Do your columns have the data type 'Whole Number' or 'Decimal Number'? If so, your own calculation should work. Anyway, it works for me:

    • CHaines's avatar
      CHaines
      Advocate I
      3 years ago

      They are both calculated columns that are decimal type. Here are some screenshots of what I'm seeing. Any idea why the formula might not be working? 
      Not working:

      Not working:

      Working:

      • CHaines's avatar
        CHaines
        Advocate I
        3 years ago

        Actually, I'll accept your reply as solution confirmation and repost this as a different issue Barthel 

    • CHaines's avatar
      CHaines
      Advocate I
      3 years ago

      I found the problem! I think I had not updated or something when I changed from 

      RoutePriceChange = AllChanges[SecondPlanRouteValue] - AllChanges[FirstPlanRouteValue]

       to having some ISBLANK() checks.