1) RED I have a workbook that on the Summary by Examiner page (second dashboard), I find some % calculations do not display when the first month of a two month comparison has no values. I looked at the calculation and it's using zn for the lookup (ex: (ZN(COUNTD([Claim Number])) - LOOKUP(ZN(COUNTD([Claim Number])), -1)) / ABS(LOOKUP(ZN(COUNTD([Claim Number])), -1)) from Overall New Claims % by Examiner sheet. How do I fix this calculation? Is there no way around the problem? It seems like there should be a way to have a calculation show an increase from zero.
Also, I noticed that when there's a percent decrease more than 100%, it doesn't display it. My boss wants to see -600% instead of -100% although I can theorize why it's displayed that way. Any solutions?
Is there a way to configure the color of the percentages so that negative value equals one color, exactly zero equals a second color, and positive value equals a third color. I don't need gradients, I actually would prefer three distinct colors I can configure, say red, dark yellow or brown and green.