Not a Number (Formula Error)

[Neal_Fluhrer]

I am simply trying to divide two numbers but am receiving an Error for a result. The numerator is a # column were the denominator is a formula.

Numerator: {{Lease Total}}

Denominator: IF(AND({Leasing Co.}=“Ascentium”,Credit=“E”),.0499,IF(AND({Leasing Co.}=“Ascentium”,Credit=“D”),.045,IF(AND({Leasing Co.}=“Ascentium”,Credit=“C”),.0325,IF(AND({Leasing Co.}=“Ascentium”,Credit=“B”),.0295,IF(AND({Leasing Co.}=“Ascentium”,Credit=“A”),.0285,IF(AND({Leasing Co.}=“Ascentium”,Credit=“P”),.0279,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“E”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“D”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“C”),.0305,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“B”),.0291,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“A”),.0281,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“P”),.0271,IF(AND({Leasing Co.}=“FDGL”,Credit=“E”),.0399,IF(AND({Leasing Co.}=“FDGL”,Credit=“P”),.0265,IF(AND({Leasing Co.}=“FDGL”,Credit=“A”),.0275,IF(AND({Leasing Co.}=“FDGL”,Credit=“B”),.0285,IF(AND({Leasing Co.}=“FDGL”,Credit=“C”),.0299,IF(AND({Leasing Co.}=“FDGL”,Credit=“D”),.0399,""))))))))))))))))))

Formula to divide the two: IF(Factor="","",{Lease Total}/Factor)

Why is there an error? How do I fix it?

Note: the formatter on both say: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.

[JonathanBowen]

Hi @Neal_Fluhrer - not exactly sure what the problem is with your formulas as I haven’t tried to recreate them, but one suggestion would be to get rid of the long nested IF statement and replace this with a SWITCH:

In my example, I’ve created a field “Leasing Co/Credit” with the formula:

{Leasing Co} & '-' & Credit

The Factor field can now be greatly simplified:

SWITCH(
  {Leasing Co/Credit},
  'Company 1-A', 0.2,
  'Company 2-A', 0.3,
  'Company 1-B', 0.4,
  'Company 2-B', 0.4,
  0
)

You don’t have to worry about getting the nesting and matching brackets correct. If you get a new company or credit you can just add lines above the default return value of 0.

Now your formula field can be:

IF(Factor > 0, {Lease Total}/Factor)

JB