Skip to main content
Question

If Formula for Null values

  • March 10, 2022
  • 0 replies
  • 66 views

Forum|alt.badge.img+3
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
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
------------------------------