Frogs in the pond - Display percentages not numbers

[Oliver J.]

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 in percentages regardless of the numbers.

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 that 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

 

[David F Cox]

There is probably a better way, but could you insert a row in the right
place and calculate the figures as a percentage of the top value?

e.g =F1/$F$1

 

[Andy Pope]

Hi,

I think you can do this in your pivot table.
So if this description matches your picture,

Row field is 'Live Quarters'
Column field is 'Entry Date'
Data field is 'Count of Frogs'

You can change the setting of 'Count of Frogs' field.
Right click and pick Field Settings.
Click the Options >> to expand dialog.
Show Data as: % Of
Base Field: 'Live Quarters'
Base Item: 1

This should then turn the values for Live Quarters 1 to 100% for all
dates. Then subsequent live quarters will be based on live quarter 1.
So 1940 will be 97% of 2000.

Cheers
Andy

 

[Oliver J.]

Good grief !
Looks like this could be the solution.
But Excel is giving me an error message telling me that one of my fields has
too many unique elements ...

I think it's my Batch arrival Quarters on the left that is causing the
problem.
In my (simplified) example you see them as quarters whereas in reality this
is a grouped field based on the arrival date of each unique frog.
It's the fact that each frog belongs to a particular Quarter that allows the
Quarter to be deduced and grouped.

I'll have to sort that out in my External data, "upstream" ...
Or try it out on a smaller sample of fake data.
Then I'll let you know.
Thanks.

O.J. & A.K.F.

 

[David F Cox]

Thanks Andy, I learned something too.

Hi,

I think you can do this in your pivot table.
So if this description matches your picture,

Row field is 'Live Quarters'
Column field is 'Entry Date'
Data field is 'Count of Frogs'

You can change the setting of 'Count of Frogs' field.
Right click and pick Field Settings.
Click the Options >> to expand dialog.
Show Data as: % Of
Base Field: 'Live Quarters'
Base Item: 1

This should then turn the values for Live Quarters 1 to 100% for all
dates. Then subsequent live quarters will be based on live quarter 1.
So 1940 will be 97% of 2000.

Cheers
Andy

 

[Oliver J.]

Thanks Andy, Spot on !!

I had tried those "% of" options but chose the wrong values and didn't
understand the results. I had given it up as "too complicated for me". Do
you know of any place where those options are explained ? With some examples
?

Excel is so much faster when you can take advantage of it's built in
features. No programming, no messing around.

Well done.

O.J. & A.KF.

Good grief !
Looks like this could be the solution.
But Excel is giving me an error message telling me that one of my fields
has too many unique elements ...

I think it's my Batch arrival Quarters on the left that is causing the
problem.
In my (simplified) example you see them as quarters whereas in reality
this is a grouped field based on the arrival date of each unique frog.
It's the fact that each frog belongs to a particular Quarter that allows
the Quarter to be deduced and grouped.

I'll have to sort that out in my External data, "upstream" ...
Or try it out on a smaller sample of fake data.
Then I'll let you know.
Thanks.

O.J. & A.K.F.

 

[Andy Pope]

Hi,

Here is a good place to start with info on PT, although I'm not sure
whether what you want is there.
http://www.contextures.com/xlPivot01.html

Also some info here.
http://www.datapigtechnologies.com/ExcelMain.htm


Cheers
Andy

Thanks Andy, Spot on !!

I had tried those "% of" options but chose the wrong values and didn't
understand the results. I had given it up as "too complicated for me". Do
you know of any place where those options are explained ? With some examples
?

Excel is so much faster when you can take advantage of it's built in
features. No programming, no messing around.

Well done.

O.J. & A.KF.