Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
trying to organize my baseball cards by 3 variables
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 --------------------------------------------------------------------------------------------- Sorry if this came off as a little confusing, but obviously I am a lot confused. :) -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php...o&userid=30611 View this thread: http://www.excelforum.com/showthread...hreadid=542996 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
trying to organize my baseball cards by 3 variables
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? -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php...o&userid=30611 View this thread: http://www.excelforum.com/showthread...hreadid=542996 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
trying to organize my baseball cards by 3 variables
"Kepf" wrote in message
... 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
trying to organize my baseball cards by 3 variables
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
trying to organize my baseball cards by 3 variables
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. :) -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php...o&userid=30611 View this thread: http://www.excelforum.com/showthread...hreadid=542996 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
constructing (complex) variables with worksheet functions | Excel Discussion (Misc queries) | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
Create random number bingo cards with Excel | New Users to Excel | |||
wild cards in formulas and functions | Excel Worksheet Functions |