8 Replies Latest reply on Mar 8, 2017 1:31 PM by Jim Dehner

# Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Hello,

Please see file attached - notice how even though Current Year (4) vs. Previous Year (0) was a 100% decrease, my calculations is returning it as a 0% decrease.

Could someone take a look?

Thanks!

Sam

• ###### 1. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Hi

I think the problem was with the change calculation -  ZN((SUM([Selected Year Events]) - SUM([Previous Year Events])) / SUM([Previous Year Events]))

Note I changed the denominator to previous year events

here are the results

Let me know if this helped

Jim

1 of 1 people found this helpful
• ###### 2. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Ha! Wow, I definitely did not pay attention to detail.

You are correct, my calculation was wrong!

Thank you!

Sam

• ###### 3. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Jim

• ###### 4. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Wait, I spoke to soon.

Notice the first row, how come the calculation is still 0% even though it increased by 1 event between the years?

Sam

• ###### 5. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Here is the workbook with calculation for your reference.

Would you be able to take a look?

Thanks!

Sam

• ###### 6. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

We needed the ) check - see this formula

Jim

If sum([Selected Year Events])>0 and sum([Previous Year Events]) = 0 then 1

elseif sum([Selected Year Events])=0 and sum([Previous Year Events]) = 0 then 0

else ZN((SUM([Selected Year Events]) - SUM([Previous Year Events])) / SUM([Previous Year Events]))End

• ###### 7. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Hi Sam, since it's not possible to divide by zero, percentage change is tricky if that's your starting point.

You could adjust your calculation to show 'N/A' in these cases, or do something else along these lines.

-- Glenn

• ###### 8. Re: Help with calculation not returning correct percentage difference. (WORKBOOK ATTACHED)

Sam I attached the workbook and also modified the formula for nulls add a ZN function on all the  sums