Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display 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 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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
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 "Oliver J." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
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. wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
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" a écrit dans le message de news: ... 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. wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
Thanks Andy, I learned something too.
"Andy Pope" wrote in message ... 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. wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
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. "Oliver J." a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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. wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Frogs in the pond - Display percentages not numbers
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 Oliver J. wrote: 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. "Oliver J." a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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. wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frogs in the pond - percentages not numbers | Charts and Charting in Excel | |||
how to display numbers in thousands i.e display 10,000 as 10 | Excel Worksheet Functions | |||
I wish to display numbers in engineering format (exponents * 3). | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
How do I display negative numbers in a differnt color font? | Excel Discussion (Misc queries) |