Hello, I am having an issue with displaying my data -- it looks one way on the data tab and a different way on the scoreboard tab when it is included as part of a text string.
Step 1 : On my data tab I create a calculated value
KPI= Variable1 / Variable2
and set the formatting to a number with no decimal places.
The formatting works as expected on the data tab, for example, a KPI of 83 shows up as 83.
So far so good, so I create a named range to reference this cell later.
Step 2 : On the scoreboard tab, I created a cell that contains a text that is structured as
= "KPI: " &KPI & "%".
So the happy path would be a visual display of
KPI: 83%.
Instead, what is displayed is
KPI: 83.3333333333333333%.
Not a problem, so I think, I just need to change the formatting on the scoreboard tab to match.........however changing the formatting is not working. I've tried General, Number (0 decimal places), and Text, and when the KPI percentage is a fraction, it displays out to something like 10 decimal places. And,not to get too technical, looks really really dumb.
Any suggestions?
Hi David
Since you are joining text with values, formatting the cell won't work.
Try this instead ="KPI: " &TEXT(KPI,"0%")
Sunny
Thanks for your quick response. Using the TEXT( ) function worked.
The other solution that I came up with was when I remembered that formatting only impacts how a value is displayed, and not the underlying value.
That's what the ROUND( ) function is for, so if I define and name the KPI formula as = ROUND ((Variable1/Variable2)*100, 0) the KPI will always be a whole number.
Solution 1 is most likely the preferred solution since the .3333333333 might be important in other calculations.
Thanks again.
Excel always uses the underlying value for calculation, not the displayed value.
