Frogs in the pond - percentages not numbers
 

Hi,
I have this little charting problem. I'm sure someone will have the
solution.
It's difficcult to explain so I 've posted a picture here
www.fransysco.net/frogs/frogs.gif
You may have to zoom on your browser to see it perfectly.

I have some data in a table and it's figures about frogs in a a pond and
about how many arrive in each batch per quarter and how long they survive.
The length of survival is measured in Quarters, too.

So each quarter there's a batch of new frogs that arrive - usually 2000 in a
a batch. And I measure the survival rate of each batch.
Now, if I always had 2000 in a batch this would be easy peasy, but it's not
always the case.

Once the delivery truck was struck by lightning and the half the frogs died.
That was in 2004/Q2, so I started out with a batch of 1000 frogs.

Another time, we ran out of mosquitoes in the breeding tank and lost three
quarters of the batch.
That was in 2005/Q1 so we started out with a batch of only 500.

You can see the data and the graph here in this picture.
www.fransysco.net/frogs/frogs.gif
And if you look at the numbers graph, you can see that 2004/Q2 and 2005/Q1
don't start off in the same place as the others. The fact that there were
less frogs in the batch is obvious. But that's not what I need to know. I
need to know the survival rate regardless of the number.

What I want to do is get the y axis expressed as a percentage of the maximum
number of frogs in the batch. That way, every batch would start off as 100%
(of itself!) and my lines would be comparable no matter how many frogs I
started out with.

Now, I can do this by putting an intermediary table in between my data table
and my graph.
On the intermediary table I calculate the percentages and base the graph on
the intermediary table.
That would be OK if my data table had fixed diemensions but in fact it is a
Pivot Table and Pivot Tables have a tendancy to change shape. So my
intermediary table would have trouble in adapting to the Pivot Table.

That's why I need the graph to read the numbers table but display the data
as percentages.
Can it be done?

Any help appreciated and Thanks in advance.

A.K. Fletcher

Frogs in the pond - percentages not numbers
 

Also see responses to multipost titled "Frogs in the pond - Display
percentages not numbers"

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi,
I have this little charting problem. I'm sure someone will have the
solution.
It's difficcult to explain so I 've posted a picture here
www.fransysco.net/frogs/frogs.gif
You may have to zoom on your browser to see it perfectly.

I have some data in a table and it's figures about frogs in a a pond and
about how many arrive in each batch per quarter and how long they survive.
The length of survival is measured in Quarters, too.

So each quarter there's a batch of new frogs that arrive - usually 2000 in a
a batch. And I measure the survival rate of each batch.
Now, if I always had 2000 in a batch this would be easy peasy, but it's not
always the case.

Once the delivery truck was struck by lightning and the half the frogs died.
That was in 2004/Q2, so I started out with a batch of 1000 frogs.

Another time, we ran out of mosquitoes in the breeding tank and lost three
quarters of the batch.
That was in 2005/Q1 so we started out with a batch of only 500.

You can see the data and the graph here in this picture.
www.fransysco.net/frogs/frogs.gif
And if you look at the numbers graph, you can see that 2004/Q2 and 2005/Q1
don't start off in the same place as the others. The fact that there were
less frogs in the batch is obvious. But that's not what I need to know. I
need to know the survival rate regardless of the number.

What I want to do is get the y axis expressed as a percentage of the maximum
number of frogs in the batch. That way, every batch would start off as 100%
(of itself!) and my lines would be comparable no matter how many frogs I
started out with.

Now, I can do this by putting an intermediary table in between my data table
and my graph.
On the intermediary table I calculate the percentages and base the graph on
the intermediary table.
That would be OK if my data table had fixed diemensions but in fact it is a
Pivot Table and Pivot Tables have a tendancy to change shape. So my
intermediary table would have trouble in adapting to the Pivot Table.

That's why I need the graph to read the numbers table but display the data
as percentages.
Can it be done?

Any help appreciated and Thanks in advance.

A.K. Fletcher