Help

NaN error occuring when trying to calculate a percentage

1325 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Tracy_Wade
4 - Data Explorer
4 - Data Explorer

Trying to to get a percentage in a column called %won. I have a basic formula of {actual amount/amount needed} however some of my actuals are $0 so NaN appears in those columns which won’t give me a total Average of % won. So I used an If statement IF({Minimum Credit Needed} = 0,{Actual Credit}, IF({Actual Credit} = 0, 0, {Actual Credit} / {Minimum Credit Needed})) Now I have some crazy percentages and my average is not even close. When any of the columns have a 0 I really just want it to not factor into the overall average. Can someone help me with the formula?
Thanks

2 Replies 2
Tracy_Wade
4 - Data Explorer
4 - Data Explorer

I’ve updated my formula to this and now the percentages are working but I’m back to getting the NaN error in the fields that have zeros. Here’s the new formula. IF({Minimum Credit Needed} = 0,{Actual Credit}/{ Credit $ Needed}, IF({Actual Credit} = 0, 0, {Actual Credit} / {Credit $ Needed}))

Dividing a number by zero is not allowed. So, if the {Credit $ Needed} is zero, you will get NaN.

If you want a number only when there is a non-zero value in both {Actual Credit} and {Credit $ Needed}, try this formula.

IF (
  AND(
    {Actual Credit},
    {Credit $ Needed}
  ),
  {Actual Credit} / {Credit $ Needed}
)