Excel Column Charts with only numbers; and summing multi-row data

Topic

New Reply

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 2-D 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 A2-A4 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

Reply | Quote

Replies

In the first case, just 2-D graph the B column. Then, select the x-axis values and from the Select Data option in Design, “Edit” your horizontal axis labels, select your A column.

43392-Clip0001

Looking for a quick (dirty?) solution to issue 2, I wrote:

43393-Clip0004

Change A7 and B7 references to be as far down as you need and then fill down column C.

Reply | Quote

In the first case, just 2-D graph the B column. Then, select the x-axis values and from the Select Data option in Design, “Edit” your horizontal axis labels, select your A column.

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.

I looked up the definition of SUMPRODUCT and I must confess it was not clear to me what it does. So I will do a little testing.

Also, I don’t understand what your ‘ ROW(>) ‘ function does.

Harry

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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)

43403-hmw

HTH,
Maud

Reply | Quote

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

Reply | Quote

Good alternative, Maud (of course).

Reply | Quote

KW,

Although I thought I have a fairly good understanding of “SumProduct”, your formula is quite elegant. Learn something every day.

Thanks,
Maud

Reply | Quote

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

Reply | Quote

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,
Maud

Reply | Quote

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

Reply | Quote

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,
Maud

Reply | Quote

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

Reply | Quote