Whole numbers to big for Graphs

[Lynda]

I have a number of different worksheets that I input data to for different
periods. My number 1 sheet has my graph and data table that I print for my
report. All my sheets are set to automatically link to my number 1 sheet. My
problem is that in one of my worksheets I am using whole numbers eg:
1,234,567.00. Of course to have this whole number fit into the rest of my
graph I need to convert it to 3 decimal places eg: 1,234.567. In the data
table under my graph I have formatted the cell to accept 1,234.567 and this
is how you see it. Unfortunately the graph still sees it as 1,234,567.00
which has the column in the graph going way off the page. It seems the only
way around this is to break the link and manually type the number in. I am
hoping this makes sense and perhaps someone can show me the magic trick.

 

[David Biddulph]

Why did you decide to convert to 3 decimal places and divide the number by
1000? What's wrong with just formatting with zero decimal places?

 

[Lynda]

Thank you for your response David. Can i give you a scenario?
This month we used 1400 items
We were allowed to use 1700
It cost us $1,234,567.00 which we reduce to 1234.567
So far this year it has cost us $3,456,789.00 which we reduce to 3456.789
They also receive a seperate table with the whole numbers on it but in the
graph and accompanying data this is how they want us to report it so a good
employee does as the boss requests. I know in this instance it is only
manually changing 2 numbers but when you have masses of different data to
input into lots of different tables it would be nice to be able to set things
up to happen automatically if possible.

 

[Jon Peltier]

Lynda -

Did you get David's point about formatting the number? You can make it look
like it's 1/1000 of its value, but the value is retained internally. You can
read a little bit about Excel's number formats here:

http://peltiertech.com/Excel/NumberFormats.html

I admit I didn't really understand from your description what your problem
really was.

- Jon

 

[Lynda]

Hi guys,

My sincerest apologies as I think I have led you astray. I do understand now
what David was saying and I can see why he was saying it because basically I
wasn’t changing anything. Perhaps if I try again it may make a bit more sense
to you. What I should have been saying is in Sheet 2 the number 1,234,567.00
is linked to the data table in sheet 1. Rather than have the whole number
displayed in the data table in sheet 1 I formatted the number with custom
formatting type 0.000,, to reduce it to 1.235. Even though the cell is
showing the number as 1.235 the chart is still reading it as 1,234,567.00 and
so the column is off the page.
Does this make more sense now that I have the numbers right.

 

[David Biddulph]

No, I'm still confused, Lynda.

As far as I can see, if you apply the format 0.000,, to the source data on
which the chart is based, the chart will pick up the same format. And even
if you leave the source data in its original format, you can apply the
custom format to the chart axis itself.

Are you saying that the basic data is on sheet 1, formatted as 1,234,567.00
and that you have used that data as the source for the graph, and also have
a table on sheet 2 which picks up the data from sheet 1 and formats it as
0.000,, ? If so the formatting on sheet 2 won't affect what's on sheet 1 or
on your graph, but you can either set the format on sheet 1 in the same way
as you have done on sheet 2 (without it affecting the undelying values), or
you can format the axis on the graph.

 

[Jon Peltier]

The format is not changing the VALUE of the number, just the APPEARANCE of
the number. So either you have to change the new numbers to match the old,
or vice versa.

You could use a calculation in the formula that links a cell on sheet 2 to
the cell on sheet 1. Instead of

=Sheet1!$A$1

use

=Sheet1!$A$1/1000000

and plot this value. Or if for some reason you need the value to remain
intact here in sheet 2, then elsewhere in sheet 2 put formulas like this
into a range and plot these cells instead of your other table.

- Jon

 

[Lynda]

Jon,

Thank you, thank you, thank you, that worked a treat.

Thank you also David.

Thank you both for your perseverance. Please keep up the good work.

Cheers
Lynda