Need to create chart from data in 2 columns and not sure how.

[Stacey]

Hello,



I have two columns of data that I want to compare. Here is an example of
how they look.

Tone
Depth

4
3

5
5

3
2

3
3

3
2

3
2

2
3

2
3

4
4

3
1

3
2

3
1

3
3

3
1

3
3

4
4

3
2

4
3

4
3

4
4

4
3

3
5

3
2




As you can see, there are several points where a Tone of 3 can have a depth
of 5 or 2 or various other numbers between 1 and 5. Tone has the same range
of numbers.



I want to create a chart that compares Tone in terms of Depth, i.e., if it
has a Tone value of 4, it will show the quantitve numbers of correlating
depth values as in (there are 2 entries with a tone of 4 and a depth of 4,
and 1 entry with a Tone of 4 and a depth of 5, and 8 entries with a tone of
4 and a depth of 3, etc.)



I want it to show this for each tone Value, 1-5. I'm not sure how I would
go about creating this chart. Could anyone help me?



As you can see, it's all in two columns, so I would like for excel to pull
out the summary data (i.e. the number of depth entries for each tone type)
by itself so that I don't have to do it manually, if possible. If I were to
do it manually, it might resemble something like this (does not match with
chart above.)



Tone 1

_______

Depth of 1=0

Depth of 2=2

Depth of 3=8

Depth of 4=1

Depth of 5=1



Tone 2

_______

Depth of 1=5

Depth of 2=7

Depth of 3=10

Depth of 4=3

Depth of 5=5



Tone 3

_______

Depth of 1=2

Depth of 2=2

Depth of 3=3

Depth of 4=8

Depth of 5=12



Tone 4

_______

Depth of 1=3

Depth of 2=2

Depth of 3=6

Depth of 4=3

Depth of 5=4



Tone 5

_______

Depth of 1=0

Depth of 2=2

Depth of 3=1

Depth of 4=0

Depth of 5=0



Let me know if you can help! I'm new to charting in Excel.



Thanks!



--Stacey

 

[Bernard Liengme]

Read about Pivot table and come back if you have questions
best wishes

 

[Guest]

Your data is 3-dimensional; tone, depth and count. Let me refer to them as
x, y and z.

Idea 1: If you can rotate one into a row (there is a funciton for that), set
up a table with one cell for each pair of x,y to hold the count. See if you
can use the "DCOUNT" function to populate the array. Setting up the criteria
for both x and y is something I have not tried personally, but I think it can
be done.

Idea 2: Create a third column that is the sum of 10*x+y. Set up an x by y
blank area, and use the "DCOUNT" function in each of those cells to count
the entries in column 3 that equal 10*x + y. You can use various
3-dimensional graphs to display this array.

 

[Guest]

Sorry, my last post is a bum steer. The dcount function is really hard to
use with those ideas, but you may be able to figure it out. The pivot tables
tip is probably better.