Forum Discussion

JenBlack's avatar
JenBlack
Qrew Member
3 years ago

If Formula for Null values

I am using a formula to calculate "Total Contract Value" for our clients. 

This is what I have so far. The problem is, where the Invoice Duration field is null, my formulas are not calculating anything. The non-null Invoice duration parts of this formula are working. So I know it's in the IsNull section of my formulas. 

Invoice frequency is a Multiple Choice field, Invoice Duration is a numeric field. If invoice duration is blank, we want the formula to automatically assume ongoing invoices. This formula field will show us the total annual contract value of said invoice amounts.

If([Invoice Frequency]="Monthly" and IsNull([Invoice Duration]), [Invoice Amount]*12,
[Invoice Frequency]="Monthly" and not IsNull([Invoice Duration]), [Invoice Amount]*[Invoice Duration],
[Invoice Frequency]="Quarterly" and IsNull([Invoice Duration]), [Invoice Amount]*4,
[Invoice Frequency]="Quarterly" and not IsNull([Invoice Duration]), [Invoice Amount]*[Invoice Duration],
[Invoice Frequency]="Annually" and IsNull([Invoice Duration]), [Invoice Amount],
[Invoice Frequency]="Annually" and not IsNull([Invoice Duration]), [Invoice Amount]*[Invoice Duration],
[Invoice Frequency]="Semi-Annually" and IsNull([Invoice Duration]), [Invoice Amount]*2,
[Invoice Frequency]="Semi-Annually" and not IsNull([Invoice Duration]), [Invoice Amount]*[Invoice Duration],
[Invoice Frequency]="One-Time" and IsNull([Invoice Duration]), [Invoice Amount],
[Invoice Frequency]="One-Time" and not IsNull([Invoice Duration]), [Invoice Amount]*[Invoice Duration])
Thanks for taking a look and any help you can provide!

------------------------------
Jen Black
------------------------------

2 Replies

  • Is the [Invoice Duration] field set to treat blank as zero in field properties?  If so uncheck that setting.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • JenBlack's avatar
      JenBlack
      Qrew Member
      Brilliant! This worked. Thanks, Mark!

      Jen

      ------------------------------
      Jen Black
      ------------------------------