Need help with complicated chart (xy chart? z axis? not sure!)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two groups of people from a survey I recently conducted, "probably"
people, and "definitely" people. Each one provided a title, as well as
choosing the price range they deemed fair for a particular service.

I want to create a chart that captures how many of each group of people have
a certain category of title and chose a specific price range. Thus, the data
to chart looks something like this:

Definitely:
$1000 $ 3000
President 2 6
VP 8 3

Probably:
$1000 $ 3000
President 4 5
VP 1 7


Any advice would be GREAT!!
 
I'm thinking you want a column chart, with the columns displayed in an
appropriate grouping. A pivot table/pivot chart is a handy way to allow
you to investigate different groupings easily. Rearrange your data like
this:

Probability Rank Amount Count
Definitely Pres 1000 2
Definitely Pres 3000 6
Definitely VP 1000 8
Definitely VP 3000 3
Probably Pres 1000 4
Probably Pres 3000 5
Probably VP 1000 1
Probably VP 3000 7

Use the Pivot Table Report item from the Data menu to create a pivot
table of this data. Put count into the data area and the other three
fields into the row area, then create a pivot chart from this table. The
default type is a standard column chart, which is fine for exploring the
groupings.

Depending on the order of row fields in the pivot table/chart (which you
can easily adjust by clicking and dragging), your category axis will
have three "rows", arranged like this:

Def. | Prob. | Def. | Prob. | Def. | Prob. | Def. | Prob. |
Pres | VP | Pres | VP |
1000 | 3000 |

or this

1000 | 3000 | 1000 | 3000 | 1000 | 3000 | 1000 | 3000 |
Def. | Prob. | Def. | Prob. |
Pres | VP |

or any number of ways (or actually 3! arrangements, 6 in all). The pivot
table makes it easy to investigate all possibilities quickly.

When you find an arrangement you like, you could then convert the pivot
table to a regular range, and create a regular chart from the converted
table.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top