Chart Grouping Problem 4

[vwhite]

I am having trouble trying to figure out how to display the following chart. I have a list of data in the following format:

Type Value
A 0.379
B 0.505
C 0.632
C 0.253
B 1.263
A 1.768
D 0.505
C 0.442
B 0.632

I want the x-axis to display each of the Type fields (in groups) and the y-axis to display the values. So that I end up with a chart with A B C D only on the x axis and each of the A values marked above A and B values above B etc. (all with the same markers)

I hope this makes sense and I hope someone can help.


....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[FrodoBaggins]

If you use the pivot table with chart function and drag Type and Value to the Row column, and value to the data area and you should get what you are looking for.

 

[vwhite]

I have tried this already - but it will only sum, count, max etc the values not just display them.

I also created a crosstab query (in Access) so that the values were displayed as follows:

Record A B C D
1 Value
2 Value
3 Value
4 Value
5 Value
6 Value

and then used a line graph.

But this has a number of problems
1. Each marker is different so I have to change them all individually and I have to delete each line between the markers. As i have many records this is very time consuming.
2. I have more than 255 records (about 1000) - the maximum allowed for the no of series

I also tried a scatter graph with the same problems and also the fact that it will not display the Types A, B , C D etc only the x axis - just numbers



....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[vwhite]

have written a marco to change all the markers to be the same and to remove the lines but I still cannot get over the fact that there are more than 255 records and therefore I cannot create more than 255 data series.

The chart should look something like this

4-| X
| X X X
2-| X X X
| X X
--------------------------->
A B C D

am I missing something really simple here?


....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[tav1035]

This looks tricky but can be accomplished by a pivotchart trick.
Create a pivotchart.

Drag 'TYPE' from the PivotTable list and drop it at the bottom of the chart.

Drag 'VALUE' from the PivotTable list and drop it on the middle of the chart.

Now your chart should sum the value.
Now for the stack trick...

Just simply drag 'VALUE' from the PivotTable list again and drop it on the legend (on the left side of the chart).

Now it should show the stacks of values.

Also you may want to consider rounding your values to the nearest .5 or 1.00 to reduce the number of stacks.

 

[SkipVought]

tav1035,

You PivotChart Stud!

=======>>> STAR!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vwhite]

You legend...

thanks mate!



....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[xlbo]

Nice trick Tav

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[vwhite]

um...unforunately this is not the end of this saga Tav...

your trick worked well for my first lot of data then the second lot had much more data i.e. there are many different VALUEs. When I put the VALUE into the Legend I get the error message

"Pivot table will not fit on sheet. Show as much as possible?"

If I answer Yes it cuts off some of the data.

Can Excel not handle it?





....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[tav1035]

vwhite,
Never tested the value limits on the 'Legend'. I just know that it is a nice trick to use especially when you have only two columns of data. I mentioned above- to 'round up' when you can(with your data), to reduce the number of values. This should keep the number of values to a smaller amount.
I guess if this doesn't work for you, then we will all have to put our heads together again...
tav

 

[vwhite]

sorry tav, I missed the rounding up suggestion offered previously (probably in my excitment at testing your solution)....

Rounding up works well to reduce the number of different values and hence keep the legend under 255 items.

thanks once again for your assistance...

....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama