Skip to main content

NaN error occuring when trying to calculate a percentage

  • July 20, 2021
  • 2 replies
  • 27 views
T

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

    T
    • Tracy_Wade
    • Author
    • New Participant
    • July 20, 2021

    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}))

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • July 21, 2021

    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}
)
    Terms & ConditionsAccessibility statement