I have a simple formula in one cell, dividing A1 into B1 and I cannot find
the correct formatting to have it read correct e.g. 71.5 The closest I get
is either 71.5% or .715. I can obviously multiply the cells by a 100 but it
seems Excel should have the above fomatting option - am I wrong?

Thanks

Did you try formatting it Number with Decimal Places = 1?

It sounds like the result of your calculation is .715, and you can't really
get excel to display that as 71.5

"darby" wrote:

I have a simple formula in one cell, dividing A1 into B1 and I cannot find
the correct formatting to have it read correct e.g. 71.5 The closest I get
is either 71.5% or .715. I can obviously multiply the cells by a 100 but it
seems Excel should have the above fomatting option - am I wrong?

Thanks

You are dealing with a returned number < 1 when you divide say 143 by 200.

The returned value is .715

Multiplying by 100 is only method AFAIK

No Custom Format that I know about.

But I am eager to learn<g


Gord Dibben MS Excel MVP


On Tue, 25 Aug 2009 13:34:01 -0700, darby
wrote:

I have a simple formula in one cell, dividing A1 into B1 and I cannot find
the correct formatting to have it read correct e.g. 71.5 The closest I get
is either 71.5% or .715. I can obviously multiply the cells by a 100 but it
seems Excel should have the above fomatting option - am I wrong?

Thanks

You do realize that a % is a fraction with a denominator of 100 don't you?
In other words, 71.5% = 71.5/100 = 0.715
Asking Excel to display the result of a division that results in 0.715 as
71.5 without either the % symbol or "/100" is asking the application to
display the result incorrectly. If you want the value to show as 71.5 and
you've identified that the value in the cell is a %, I suspect the best
solution will be to multiply by 100.

Steve


"darby" wrote in message
...
I have a simple formula in one cell, dividing A1 into B1 and I cannot find
the correct formatting to have it read correct e.g. 71.5 The closest I
get
is either 71.5% or .715. I can obviously multiply the cells by a 100 but
it
seems Excel should have the above fomatting option - am I wrong?

Thanks

"Steve Yandl" wrote:
Asking Excel to display the result of a division that results in
0.715 as 71.5 without either the % symbol or "/100" is asking
the application to display the result incorrectly.

So by your reasoning, you believe formats like "0," and "0,," are wrong
because they cause Excel to display values like 1,234,567 incorrectly?

Rhetorical question. Your rationale is simply flawed, IMHO. If Excel can
scale the appearance of values by dividing by 1000 or 1,000,000, I cannot
think of any philosophical reason why it could not scale the appearance of
values by multipying by 100, for example. The fact is: that kind of
scaling is at least as useful as the other scaling, based on the relative
frequency of postings for each.

(Personally, I do not like displaying percentages without "%". But I have
seen it all too often in financial reports, namely in a column labeled
"percentage of...".)

I suspect the greatest impediment, besides MS's inertia when it comes to
useful improvements, is thinking of a meaningful, unambiguous and
backward-compatible syntax for a custom format that would scale by
multiplication. For example, %00.0 already behaves a certain way.


----- original message -----

"Steve Yandl" wrote in message
...
You do realize that a % is a fraction with a denominator of 100 don't you?
In other words, 71.5% = 71.5/100 = 0.715
Asking Excel to display the result of a division that results in 0.715 as
71.5 without either the % symbol or "/100" is asking the application to
display the result incorrectly. If you want the value to show as 71.5 and
you've identified that the value in the cell is a %, I suspect the best
solution will be to multiply by 100.

Steve


"darby" wrote in message
...
I have a simple formula in one cell, dividing A1 into B1 and I cannot find
the correct formatting to have it read correct e.g. 71.5 The closest I
get
is either 71.5% or .715. I can obviously multiply the cells by a 100 but
it
seems Excel should have the above fomatting option - am I wrong?

Thanks

I've also seen reports where the column is labelled as "percentage of..."
and I'll concede that most of the time, the information intended by the
author of the report is interpreted as that author intended, particularly if
all or most of the values in the column are greater than 1%. However, if
the column were to contain many values like .02 or .003 and the audience
reading the table has good mathematical understanding of the concept of
percent, I think there is some ambiguity where some will wonder if you meant
2% or 2 hundredths of a percent etc.

When you scale a set of values by some factor, you're typically doing so on
numbers that have some unit associated with the data. For example, if you
say the values in the column represent thousands of dollars, it's very clear
that 100 in the column represents $100,000. In the case of %, the values
represent ratios and there are never units attached to them, nor is it
always clear what is meant if you're working with fractional portions of 1%
and don't include the % adjacent to the specific data entry.


Steve




"JoeU2004" wrote in message
...
"Steve Yandl" wrote:
Asking Excel to display the result of a division that results in
0.715 as 71.5 without either the % symbol or "/100" is asking
the application to display the result incorrectly.

So by your reasoning, you believe formats like "0," and "0,," are wrong
because they cause Excel to display values like 1,234,567 incorrectly?

Rhetorical question. Your rationale is simply flawed, IMHO. If Excel can
scale the appearance of values by dividing by 1000 or 1,000,000, I cannot
think of any philosophical reason why it could not scale the appearance of
values by multipying by 100, for example. The fact is: that kind of
scaling is at least as useful as the other scaling, based on the relative
frequency of postings for each.

(Personally, I do not like displaying percentages without "%". But I have
seen it all too often in financial reports, namely in a column labeled
"percentage of...".)

I suspect the greatest impediment, besides MS's inertia when it comes to
useful improvements, is thinking of a meaningful, unambiguous and
backward-compatible syntax for a custom format that would scale by
multiplication. For example, %00.0 already behaves a certain way.


----- original message -----

"Steve Yandl" wrote in message
...
You do realize that a % is a fraction with a denominator of 100 don't
you?
In other words, 71.5% = 71.5/100 = 0.715
Asking Excel to display the result of a division that results in 0.715 as
71.5 without either the % symbol or "/100" is asking the application to
display the result incorrectly. If you want the value to show as 71.5
and you've identified that the value in the cell is a %, I suspect the
best solution will be to multiply by 100.

Steve


"darby" wrote in message
...
I have a simple formula in one cell, dividing A1 into B1 and I cannot
find
the correct formatting to have it read correct e.g. 71.5 The closest
I get
is either 71.5% or .715. I can obviously multiply the cells by a 100
but it
seems Excel should have the above fomatting option - am I wrong?

Thanks