Compound Formula Help

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
My formula needs to add another field once a calculation has occured. This formula creates the total impressions. Sometimes there is no line item value but we just want to add impressions which is what the field "Append Impressions" is for.

The formula below is my attempt to say:
If Entertainment is chosen, use CPM version 1 then add the field Append Impressions to that CPM total.

The formula works fine when there is value in either Line Item Value field or Product-CPM field. But it does not return just the Append Impressions value when the other two fields are 0 - which is what I need it to do.

If(
[RFP/IO - Brand Category]="Entertainment",
(([Line Item Value]*1000)/[Product - Minimum CPM ENT]) + [Append Impressions],
(([Line Item Value]*1000)/[Product - Minimum CPM]) + [Append Impressions])
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Photo of KenFirch

KenFirch

  • 10 Points
I think you're getting a 'divide by zero' error. Try:

If([RFP/IO - Brand Category]="Entertainment",
If(Nz([Product - Minimum CPM ENT])=0,[Append Impressions],([Line Item Value]*1000/[Product - Minimum CPM ENT]) + [Append Impressions]),
If(Nz([Product - Minimum CPM])=0,[Append Impressions],([Line Item Value]*1000/[Product - Minimum CPM]) + [Append Impressions])
)
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
I knew that was the issue but I was at a loss on how to solve it. Your formula worked perfectly!!! Many thanks for helping my head to not explode (today).