Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Excel Column Charts with only numbers; and summing multi-row 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 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

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.

    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; 2016-01-25 at 21:14.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    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

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    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

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 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

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Good alternative, Maud (of course).

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

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

    Thanks,
    Maud

  9. #9
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    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

  10. #10
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    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
    Attached Files Attached Files

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 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,
    Maud
    Last edited by Maudibe; 2016-01-28 at 10:17.

  12. #12
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    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

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 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,
    Maud
    Last edited by Maudibe; 2016-01-28 at 20:41.

  14. #14
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •