Results 1 to 14 of 14

20160125, 18:46 #1
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
Excel Column Charts with only numbers; and summing multirow data
I have Excel 2007 (part of Office 2007) running on a Windows 7 PRO (64 bit) computer.
I have two questions which appear simple to me, but I cannot determine how to get Excel to handle them correctly.
The first relates to creating a 2D column chart (clustered or otherwise).
Let’s assume my entries are as shown below, starting in row 1, and the numbers are in columns A & B.
Row.........A........B
1.............150.....$200
2...............45.....$27
3...............89.....$76
etc.
[sorry for the dots, but I could not determine any other way to get the columns to display correctly in my post.]
I want the numbers in column A to be on the X (horizontal) axis, and the numbers in column B to be on the Y (vertical) axis. But no matter what Category choice I use to format the cells in column A, Excel plots the ROWS on the X axis, and the numbers for both columns A & B on the Y axis. The only way I can get it to work as I want it is to change the numbers in column A to ‘words’; i.e., Sam, Bill, Henry, etc. Excel will chart that correctly. Then I go back and change the entries in column A from words to numbers, Excel makes the changes on the chart, and I am all set.
There must be a way to do this directly with two columns of numbers. Does anyone know how to do that?
My second problem deals with calculations. Again, assume my entries are as shown below, starting in row 1, and the numbers are in columns A & B. The numbers is column A are years, and I set up a Custom category (yyyy) for this.
Row.........A..........B..........C
1.............2016.....$200
2.........................$100
3.........................$75
4.........................$225
5.............2017......$654
6.........................$223
7.............2018......$432
etc.
I want Excel to sum the values for 2016 (B1 through B4) and put the result in C1; sum the values for 2017 (A5 through A6), and put the result in C2; etc. In other words, sum all the values in column B that refer to the year 2016 and place the result in C1, sum all the values in column B that refer to the year 2017 and place the result in C2, etc.
I have not been able to determine how to do this. I even tried filling in A2A4 with the year 2016, and A6 with the year 2017, etc. Still can’t get Excel to make the calculation.
Can anyone tell me how this might be done, if it can, within Excel?
Thanks so much in advance for any help on either of these problems,
Harry

20160125, 19:56 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 30
 Thanked 61 Times in 57 Posts
In the first case, just 2D graph the B column. Then, select the xaxis values and from the Select Data option in Design, "Edit" your horizontal axis labels, select your A column.
Clip0001.jpg
Looking for a quick (dirty?) solution to issue 2, I wrote:
Clip0004.jpg
Change A7 and B7 references to be as far down as you need and then fill down column C.Last edited by kweaver; 20160125 at 20:14.

20160126, 11:34 #3
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
Thanks for your very prompt reply. I see what you did here, and I now understand the logic. It just seems strange that Excel does that automatically when names are entered in Column A, but not for numbers.
Looking for a quick (dirty?) solution to issue 2, I wrote:
Change A7 and B7 references to be as far down as you need and then fill down column C.
Also, I don't understand what your ' ROW(>) ' function does.
Harry

20160126, 12:58 #4
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 30
 Thanked 61 Times in 57 Posts
The ROW function returns the row number. So, on row #1 of the sheet, the function returns a 1.
So, I added the row number to 2015 to start the comparison at 2016.
SUMPRODUCT find the total (SUM) of the result of the PRODUCT of the tests in the parens.
Often more convenient than using an array formula.

20160126, 15:05 #5
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
kweaver:
Thanks again for being so prompt.
I think I now understand what you have done. I'll do some experimentation and let you know how it goes.
Harry

20160126, 20:02 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
hmw,
If you are going to fill all the years in col A as KW suggests then another approach for the formulas in column C would be:
C1 =SUMIF($A$1:$B$7,2016,$B$1:$B$7)
C2 =SUMIF($A$1:$B$7,2017,$B$1:$B$7)
C3 =SUMIF($A$1:$B$7,2018,$B$1:$B$7)
hmw.png
HTH,
Maud

20160126, 20:38 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 30
 Thanked 61 Times in 57 Posts
Good alternative, Maud (of course).

20160126, 21:09 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
KW,
Although I thought I have a fairly good understanding of "SumProduct", your formula is quite elegant. Learn something every day.
Thanks,
Maud

20160126, 23:17 #9
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
Maudibe:
Thanks for the alternative. I will experiment with both suggestions to make sure I have a better understanding of the logic behind this.
Harry

20160127, 23:10 #10
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
maudibe & kweaver:
I set up some samples (which helps me understand what you did and why) and both of your methods work perfectly. In fact, they work fine for something I didn't ask for: numbers out of sequence.
For example:
Column A [Format: General]
2016
2016
2017
2016
2018
2017
etc.
Work fine. That saves me a sorting step.
But I am working with Bond maturity dates, which are noted as something like: 6/1/2018. So I thought I could use the YEAR function, and keep all the dates in a Date format. But that does not seem to work.
Take a look at the attachment. Column A has my starting dates shown in the m/d/yyyy Date format shown. Column F is the actual formula I used. Columns B through D are the results column in the format shown. Only when I specify the results in the General (or Number) format will I get the correct answer.
Why won't Excel display the correct answer in the 'yyyy' Date Format? I did an internet search, but could not find anything that addressed the problem.
Harry

20160128, 06:09 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
Harry,
Your formula in B4, for example, is =YEAR(A4) which returns 2015. Because you want that value formatted as a date, it converts 2015 (serial date) to 2,015 days after 1/1/1900 and returns the date 7/7/1905.
HTH,
MaudLast edited by Maudibe; 20160128 at 09:17.

20160128, 14:22 #12
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
Maud:
I'm confused, and I obviously don't understand the Excel logic here.
The information in A4 (3/12/2015) is in m/d/yyyy format. If I convert it to a number, which I assume that you are saying the YEAR() function does, it is 42075; not 2015.
So I need a little more help.
Harry

20160128, 19:36 #13
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,560
 Thanks
 111
 Thanked 621 Times in 566 Posts
Harry,
Yes, Year(A4) returns a number in general format not a date. Let me try to explain better. When you abstract the year from 3/12/2015 you end up with the number 2015. If you attempt to reformat the number 2015 as a date, you end up with 7/7/1905. Your can demonstrate this by typing 2015 in any cell then changing the format to a date. The cell displays again, 7/7/1905.
This occurs because of the way Excel handles dates. Starting with the date 1/1/1900 equals the serial number 1, 1/2/1900 equals 2, ..., 7/7/1905 equals the serial number 2015 or 2,015 days after 1/1/1900. For this reason, Excel can perform arithmetic on dates.
example 1/15/2016 + 7 = 1/22/2016 translated to 42384 +7=42391 or 42,391 days since 1/1/1900.
In your above example, 3/13/2015= serial number 42075 where 7/7/1905= serial number 2015
HTH,
MaudLast edited by Maudibe; 20160128 at 19:41.

20160128, 23:05 #14
 Join Date
 Jul 2013
 Posts
 168
 Thanks
 5
 Thanked 0 Times in 0 Posts
Maud:
Thanks for the clarification. It is much clearer now what I can and cannot do with the Excel date functions. I guess perhaps I had never tried to change from Dates to General before, and therefore never saw the problem.
Harry