Brian Barker wrote:
At 21:22 18/11/2007 -0500, Tom Webb wrote:
Alan Boba wrote:
Alan Boba wrote:
If I'm using numbers like 1,000,000,000 (one billion),
2,000,000,000
(two billion), etc. and I want them to display on a graph axis as
1.0 B, 2.0 B, etc. is there a format I can apply to the graph axis
to make the numbers display like that? If yes, what is the format
code?
Brian Barker wrote:
I don't see any way, in fact, to get the values on the chart to be
different from those in the data range. In other words, as you need
one billion to appear not as itself but as "1.0" (irrespective of
the
"B"), you will need to construct a new data range first with the
values scaled down by a factor of one billion and then use that to
plot the chart. If this means creating a new column or columns with
Alan Boba wrote:
Thanks Brian I'm sure this will work. I had thought of it before
posting
but was hoping to find if there is a way to "scale" the number
displayed on the chart axis without resorting to an extra column.
I think it's a great capability because the data can be maintained in
its native form. The display of the data is altered as necessary for
display depending on the output requirements, e.g. audience, form
factor (small sheet of paper, low resolution on TV monitor, big high
def wall projection, etc.). And with this method there's no need to
create new data, extra columns, whenever a new display format is
needed.
Joe Conner wrote:
Did you consider using scientific notation for you x-axis?
Hadn't thought of that. It works of course. The audience is the problem
though. Business users are comfortable with "B" and "M" and "K" after
the dollars, like $1.5B, $1.5M, $1.5K. Having that "E" in there and a
power of ten after isn't going to help them read the chart. If I'm
ever in
front of the right audience I can use it.
In the mean time I'll do the extra column and put in an enhancement
request.
Alan:
I played around with the formatting on this issue and was able to
generate a format 1.0 , 1.5, etc. without resorting to a new column.
The format code is: #\.#,,
The "B" for billion (or "Billion") would have to be added to the axis
title. This can be set within the chart if you break the link to the
data for the Y-axis by un-checking the source format.
TomW
Aha, well done for finding the dodge! I'd missed that. But this
doesn't quite work for me. You need three commas, in fact, to scale
the value by 10^9, and if you put that explicit point in using "\."
everything goes awry: the scaling is only by 10^7 (or 10^10 with the
extra comma). To correct this, just take out that backslash and add
the additional comma:
#.#,,,
Since the original request wanted one fractional place, change at
least one of those hashes to zeroes. And you can even put the
required "B" into the format:
0.0,,, B
Oh, and you could even use conditional brackets in the format code to
create the forms with "M" and "K" automatically where appropriate if
you wished:
[<1e6]0.0, \K;[<1e9]0.0,, \M;0.0,,, \B
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Ah! I forgot I was only dealing in millions not billions when I got
this to work. In fact, I had found that using the third comma for
billions introduces an unwanted side affect that I had struggled with
earlier in the day. When I use a third comma, it appends the previous
two commas to the result. e.g 1.2,,
Also, I found that what I had used for a format code yesterday was
different this morning when I opened my test file. It had changed to
#"."#,, from #\.#,,
Another problem I had yesterday morning was that I could not delete
user-defined format codes. I would go down through and delete them in
the dialog window, but if I closed the dialog and re-opened it, the
format codes were back. I tried saving and exiting calc, then reopening
the file, but I still could not keep them deleted. Last evening the
deletion worked properly.
Any user-defined formats are stored local to the workbook you are
using. I did a little looking under the hood last night. I found how
the format codes are stored in the "styles" file to the workbook. What
I found only sheds some light on how the codes are stored. I evidently
did not find all the pieces, because I did not find how it stores the
commas, hashes, etc. I will have to dig a little deeper sometime. I am
not up to speed on xml, so that might be part of the problem.
TomW
WinXP Sp2 OO2.3
