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