+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 24

Help with Excel 2007 formula - Divide SUM of Cells

  1. #1
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Help with Excel 2007 formula - Divide SUM of Cells

    I am very rusty with Excel so here goes...
    I am using Excel 2007

    I need to be able to devide the sum of cells E, G, I, K, M, O, Q, Sand S and U by Cell D and present the result in Column A in each of the rows.

    I would like the results presented in % rounded to 2 digits to the right of the decemil point. Such as 16.666666% would display as 16.67%.

    I will have many rows (256 of them) and want the formula to work the same in all rows starting with A2 and giving the results for that row seperately.

    I tried
    Please Login or Register  to view this content.
    but didn't work. Like I said above, I am rusty with Excell.
    Last edited by N7AS; 06-15-2019 at 01:06 AM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,267

    Re: Help with Excel 2007 formula

    Welcome to the board.

    Please take a few minutes to read the Forum Rules about thread titles, and then amend yours accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,336

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    There are two components of a cell address, vis. column(A, B,...) and row (1,2,...) --> A1, A2, AA113, etc
    =(E1+G1+I1+K1+M1+O1+Q1+S1)/D1
    or,
    =SUM(E1,G1,I1,K1,M1,O1,Q1,S1)/D1
    Last edited by protonLeah; 06-10-2019 at 12:03 AM.
    Ben Van Johnson

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,144

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Or this...
    =(E1+G1+I1+K1+M1+O1+Q1+S1)/D1
    OR
    =SUM(E1,G1,I1,K1,M1,O1,Q1,S1)/D1

    OR, depending in what is in the intervening cells,,,
    =SUM(E1:S1)/D1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,290

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I am posting the links for you.)

    https://www.excelguru.ca/forums/show...e-SUM-of-Cells

    https://www.ozgrid.com/forum/forum/h...e-sum-of-cells
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Quote Originally Posted by protonLeah View Post
    There are two components of a cell address, vis. column(A, B,...) and row (1,2,...) --> A1, A2, AA113, etc
    =(E1+G1+I1+K1+M1+O1+Q1+S1)/D1
    or,
    =SUM(E1,G1,I1,K1,M1,O1,Q1,S1)/D1
    I used
    Please Login or Register  to view this content.
    in A2. Now I need to copy a range in column A so each row is referenced correctly as
    Please Login or Register  to view this content.
    All the way down to A238.

    I just forgot how to do that.
    Last edited by N7AS; 06-10-2019 at 09:31 AM.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,267

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    The fill handle is the small square the bottom right of the selection marquee. Grab that and pull down.

  8. #8
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    I don't see a small square fill handle.
    I remember doing it differently by highlighting the cell holding shift and the down arrow until it highlights all the way down where I want to stop.
    I need to reference each row.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,290

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Look at the screenshot.
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Thanks. I found it.

    Now I need to format Column A so that numbers are rounded 2 places to the right of the decimal point.
    Such as 16.6667 becomes 16.67

    Also, color a Column with some color all the way down.

  11. #11
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    I have it formatted the way I like it.
    Thanks for everyone's help.

  12. #12
    Registered User
    Join Date
    06-09-2019
    Location
    Prescott Valley, AZ
    MS-Off Ver
    2007
    Posts
    10

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Well I thought this was solved but as I took the time to review the results they were way off just by looking at them.

    I used the method as suggested by grabbing the fill handle and drug it down.

    My formula starts in A2 and is:
    Please Login or Register  to view this content.
    and I expected A3 to have:
    Please Login or Register  to view this content.
    and A4 to have:
    Please Login or Register  to view this content.
    and so on all the way down changing to the respected row.

    I found every one was wrong.

    SO, I am asking how to fix this?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    26,290

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    In what way were the 'wrong'? What did you get instead of your expected results?

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,228

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    Are calculations set to Manual?
    Dave

  15. #15
    Forum Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,043

    Re: Help with Excel 2007 formula - Divide SUM of Cells

    And, BTW, have you read the post about cross posting???

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1