NaN error occuring when trying to calculate a percentage

323 2
Showing results for 
Search instead for 
Did you mean: 
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?

2 Replies 2

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 (
    {Actual Credit},
    {Credit $ Needed}
  {Actual Credit} / {Credit $ Needed}