convert number to % using only custom number format challenge


B

Brotherharry

I have a chart with about 6 sets of data in it.
The values between sets vary widely e.g. one set has data in the
millions, the others have data in the thousands but one in data in %
i.e. less than 1.
I can split the sets of data across two axis, so the millions stuff
goes on one axis and the thousands on the other, so I can see side by
side.
However, the % data series is obviously too small to appear and I've
run out of axis to show it on

So, I thought, what if I multiply the source % figure by 10,000 to get
be a number in the thousands so it can be plotted on my thousands
axis, but then apply a custom format so I can still see the 'real' %
figure on the data label?

ie. a custom number format that would look something like this [ # /
10,000] %

any ideas or workarounds?
 
Ad

Advertisements

B

Brotherharry

Nice, although complex. Doesn't make it easy to compare lines in the
same space as your eye has to jump between all three panels.

http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html

I have a chart with about 6 sets of data in it.
The values between sets vary widely e.g. one set has data in the
millions, the others have data in the thousands but one in data in %
i.e. less than 1.
I can split the sets of data across two axis, so the millions stuff
goes on one axis and the thousands on the other, so I can see side by
side.
However, the % data series is obviously too small to appear and I've
run out of axis to show it on
So, I thought, what if I multiply the source % figure by 10,000 to get
be a number in the thousands so it can be plotted on my thousands
axis, but then apply a custom format so I can still see the 'real' %
figure on the data label?
ie. a custom number format that would look something like this [ # /
10,000] %
any ideas or workarounds?
 
J

Jarek Kujawa

yet another hint:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

also have a look at

http://peltiertech.com/Excel/Charts/DummySeries.html

Nice, although complex. Doesn't make it easy to compare lines in the
same space as your eye has to jump between all three panels.

I have a chart with about 6 sets of data in it.
The values between sets vary widely e.g. one set has data in the
millions, the others have data in the thousands but one in data in %
i.e. less than 1.
I can split the sets of data across two axis, so the millions stuff
goes on one axis and the thousands on the other, so I can see side by
side.
However, the % data series is obviously too small to appear and I've
run out of axis to show it on
So, I thought, what if I multiply the source % figure by 10,000 to get
be a number in the thousands so it can be plotted on my thousands
axis, but then apply a custom format so I can still see the 'real' %
figure on the data label?
ie. a custom number format that would look something like this [ # /
10,000] %
any ideas or workarounds?- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -
 
J

Jarek Kujawa

also browse

microsoft.public.excel.charting

for possible answers


yet another hint:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

also have a look at

http://peltiertech.com/Excel/Charts/DummySeries.html

Nice, although complex. Doesn't make it easy to compare lines in the
same space as your eye has to jump between all three panels.
http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html
I have a chart with about 6 sets of data in it.
The values between sets vary widely e.g. one set has data in the
millions, the others have data in the thousands but one in data in %
i.e. less than 1.
I can split the sets of data across two axis, so the millions stuff
goes on one axis and the thousands on the other, so I can see side by
side.
However, the % data series is obviously too small to appear and I've
run out of axis to show it on
So, I thought, what if I multiply the source % figure by 10,000 to get
be a number in the thousands so it can be plotted on my thousands
axis, but then apply a custom format so I can still see the 'real' %
figure on the data label?
ie. a custom number format that would look something like this [ # /
10,000] %
any ideas or workarounds?- Ukryj cytowany tekst -
- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -
 
R

Ron Rosenfeld

I have a chart with about 6 sets of data in it.
The values between sets vary widely e.g. one set has data in the
millions, the others have data in the thousands but one in data in %
i.e. less than 1.
I can split the sets of data across two axis, so the millions stuff
goes on one axis and the thousands on the other, so I can see side by
side.
However, the % data series is obviously too small to appear and I've
run out of axis to show it on

So, I thought, what if I multiply the source % figure by 10,000 to get
be a number in the thousands so it can be plotted on my thousands
axis, but then apply a custom format so I can still see the 'real' %
figure on the data label?

ie. a custom number format that would look something like this [ # /
10,000] %

any ideas or workarounds?

Have you considered using a semi-logarithmic scale?

You could multiply your percent figure by 100,000; and then use this custom
format:

0.0,\% (to display with one decimal).

--ron
 
Ad

Advertisements

B

Brotherharry

Aha, that's exactly what I was looking for.

I multiplied my % values by 100,000 and plotted them directly,
removing the marker points and lines, but adding formatted data labels
using your custom number format. Lose some control over the position,
but adequate for my needs right now.
 
Ad

Advertisements

R

Ron Rosenfeld

Aha, that's exactly what I was looking for.

I multiplied my % values by 100,000 and plotted them directly,
removing the marker points and lines, but adding formatted data labels
using your custom number format. Lose some control over the position,
but adequate for my needs right now.

Glad to help. Thanks for the feedback.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top