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, each bar is
r divided in two by the yes/no answer)

o
f

c 4
a 3
r 2
d 1
s 87 88 89 90 91 92 93----->

year by year


---------------------------------------------------------------------------------------------





Sorry if this came off as a little confusing, but obviously I am a lot confused.

[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]

"Kepf" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
--
David Biddulph

[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 wrote:
> 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?
>
>
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[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.