Good Morning,
I have a monthly report that uses a pivot table to show Current Year Actuals, Our Business Plan, and Prior Year Actuals and the differences between them so We can see drill into where we missed or exceeded Plan and PY. (Note, we are on a fiscal calendar and currently in 2018)
Normally when we look at this, we use "=2018-Plan" and "=2018-2017" to calculate the difference. This means where we exceeded Plan or PY, the result is positive and where we missed the result is negative which is intuitive and what the business is used to seeing.
The issue I am having in this new report, is that when using Show as Difference From I have to tie both Plan and PY back to 2018 in the same table, I am stuck with it calculating as ="Plan-2018" and "=PY-2018" and therefor the value sign is flipped and we have negatives where we exceed and positives where were are short.
I have added conditional formatting (green for Negative and red for Positive) to make it easier to understand. I then found posts online showing me how to use number formats to visually flip the sign so it looks correct, but the actual values in the cell are still incorrect. This works well for working in the file to drill down, but when someone uses look up or copies the values into another table to make a presentation, the signs are backwards.
Is there anyway to make this calculate the way we want?
Thank you in advance for you help
I have a monthly report that uses a pivot table to show Current Year Actuals, Our Business Plan, and Prior Year Actuals and the differences between them so We can see drill into where we missed or exceeded Plan and PY. (Note, we are on a fiscal calendar and currently in 2018)
Normally when we look at this, we use "=2018-Plan" and "=2018-2017" to calculate the difference. This means where we exceeded Plan or PY, the result is positive and where we missed the result is negative which is intuitive and what the business is used to seeing.
The issue I am having in this new report, is that when using Show as Difference From I have to tie both Plan and PY back to 2018 in the same table, I am stuck with it calculating as ="Plan-2018" and "=PY-2018" and therefor the value sign is flipped and we have negatives where we exceed and positives where were are short.
I have added conditional formatting (green for Negative and red for Positive) to make it easier to understand. I then found posts online showing me how to use number formats to visually flip the sign so it looks correct, but the actual values in the cell are still incorrect. This works well for working in the file to drill down, but when someone uses look up or copies the values into another table to make a presentation, the signs are backwards.
Is there anyway to make this calculate the way we want?
Thank you in advance for you help
