Forum Discussion

CHaines's avatar
CHaines
Icon for Advocate I rankAdvocate 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.

  • 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
    Icon for Solution Sage rankSolution Sage

    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
      Icon for Advocate I rankAdvocate I

      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
        Icon for Advocate I rankAdvocate I

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

    • CHaines's avatar
      CHaines
      Icon for Advocate I rankAdvocate I

      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.