trying to organize my baseball cards by 3 variables

[Kepf]

Chart question, I am stumped:


Column A either says "yes or no"
Column B is the name of the player
Columns C through Z are the years, with a number that corresponds with
how many I have of that player for that year. (1987, 1988, 1989, etc)

Kinda like this:
----------------------------------------------------------------------------

A B C D E F-----Z

sell? playername 1987 1988 1989 1990
no Barry Bonds 4 6 2 1
yes Ken Griffey Jr 6 1 3 3
yes Sammy Sosa 1 1 5 2
no Barry Larkin 1 1 2 1
---------------------------------------------------------------------------
My question is, How do I make a column bar graph that adds up all of
column C, all of column D, etc etc through Column Z (by the year), to
produce a graph that shows:

one axis: number of cards
the other axis (the columns): year, but divided into the yes/no of
column A.

kinda like this:
---------------------------------------------------------------------------------
n
u
m
b
e (then in here, the bars are
r divided in two by the yes/no answer)

o
f

c
a
r
d
s 87 88 89 90 91 92 93----->

year by year

 

[Kepf]

wow... So maybe it isn't so crazy that I was stumped on this one.

Well, can anybody tell me if they think what I want to do is even
possible?

If not, would there be a better way I could set this up to achieve the
same results?

 

[David Biddulph]

wow... So maybe it isn't so crazy that I was stumped on this one.

Well, can anybody tell me if they think what I want to do is even
possible?

If not, would there be a better way I could set this up to achieve the
same results?

For your yes total in column C, use =SUMIF($A$2:$A$5,"yes",C$2:C$5)
For your no total in column C, use =SUMIF($A$2:$A$5,"yes",C$2:C$5)

[obviously change the row 5 references to whatever the last row of your list
is)]

Copy these across to the other columns, & then plot those totals in your
column graph.

 

[Andy Pope]

Hi,

Assuming your data table is in the range A1:Z30, so you have 29
different players. You will need to produce summary information from
which to construct your chart.

Place the following formula in cells below you table. Once you
understand how to create the summary and chart I suggest you move the
formula to another worksheet so you an continue to expand your data
table. Remember to also expand the formula though.

B33: =Yes
B34: =No

C32: =C1
C33: =SUMPRODUCT(($A$2:$A$30=$B33)*(C$2:C$30))
C34: =SUMPRODUCT(($A$2:$A$30=$B34)*(C$2:C$30))

Select C32:C34 and drag across to column Z to extend the summary table.

Now create a normal clustered column chart on the range B32:Z34

Cheers
Andy

 

[Kepf]

Thank you all very much for your quick replies.

I came to the same conclusion you did, it seems.

I just added all the yes's up in one row with the sum function, then
did the same with the no's.

Then I layered the two for each column bar.

I think my above request was just "making life too hard" when clearly
there was a smarter way of achieving the same result, which you two
pointed out above.

Now that I have created the chart though, I can't seem to find a place
for the numbers that looks good. I have the yes's on the bottom, and
the no's on the top. There is a number in the column for the yes's,
no's, and the total of the two. Any suggestions as to where to place
the 3 numbers for each column that might look better, and how?

Thanks again for all of your help and advice.