I have a chart with two percentage columns (min, max), and a third which is supposed to show the min-max range in absolute values. I use the formula =A2*60&" - "&B2*60
in cell C2
. The expected result is (for an absolute max of 60):
Min | Max | Range
33.3% | 50.0% | 20 - 30
Here's what I get:
Min | Max | Range
33.3% | 50.0% | 2000.0% - 3000.0%
The percentage columns are formatted to display a percentage value with one decimal, and this format is somewhat applied to the text.
If I change the formula to =A2*$C$1&" - "&B2*$C$1
, I get:
Min | Max | 60
33.3% | 50.0% | 20.000 - 30.000
For every format on C1
I get 3 decimals in the expression, except if I set the format to only one decimal in C1
, I really get one decimal in the expression.
Question: How do I choose how the numbers are displayed in C2
?
Best Answer
Using extra cells (which can be hidden) appears to be the only way I have found to control the Automatic formatting that Numbers applies.
In separate cells for each desired number, do the calculation.
A2*60
then in another cell,B2*60
In your
Range
column, concatenate the results. So if the cells containing20
and30
are inE2
andF2
thenRange
would have this formula:E2 & " - " & F2
or:
concatenate(E2," - ",F2)
Then if desired, hide E and F columns.