+ Reply to Thread
Results 1 to 24 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,649

    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/MSO2007;Win10/MSO2016
    Posts
    11,048

    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
    49,991

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite 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,649

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    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, Office 365
    Posts
    11,597

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

    Are calculations set to Manual?
    Dave

  15. #15
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

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

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

  16. #16
    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

    It seems, in my opinion that this forum is not too friendly to me.

    Anyway, I fixed it.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

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

    Quote Originally Posted by Pepe Le Mokko View Post
    And, BTW, have you read the post about cross posting???
    The OP did not need to post the links: when members are new here, we do it for them. That's what I did. The OP PMd me for clarification, and I explained this to him, which is why there has been no further mention of it here.

    It seems, in my opinion that this forum is not too friendly to me.
    I am sorry you get this impression. We have a set of simple rules and we expect our members to abide by them.

    In post #13 and pos #14 we asked for further details so that we could help you - you didn't respond to those requests for further details. Did you not respond because you thought we were being unfriendly?

  18. #18
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,048

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

    In post #1 you said: "... I would like the results presented in % rounded to 2 digits to the right of the [decimal] point. Such as 16.666666% would display as 16.67% ..."

    However your formula: =SUM(E2,G2,I2,K2,M2,O2,Q2,S2,U2)/D2*100
    is multiplying the quotient by 100 which seems to cancel the %.

    Note that "=SUM(E2,G2,I2,K2,M2,O2,Q2,S2,U2)/D2*100" is not the same as "=SUM(E2,G2,I2,K2,M2,O2,Q2,S2,U2)/(D2*100)"

    However, you did not specify what is wrong, i.e., what you got vs. what you expected...

  19. #19
    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 actuall get the results as a percent.

    =SUM(E2,G2,I2,K2,M2,O2,Q2,S2,U2)/D2*100 works.

    I will attempt to upload my Workbook.
    Attached Files Attached Files
    Last edited by N7AS; 06-13-2019 at 08:33 PM.

  20. #20
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,048

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

    You said you wanted the results in %.
    Notice that column A is not formatted as % but as General
    So, for example in row 1 the sum returns 1
    You divide by 10 -> 0.1
    BUT, then you multiply by 100 --> 10, this is not a %
    If you format column A as % that 10 becomes 1000% (!)

    deleting the *100 from the formula and formatting colA as % should give you what you want.

    Also, you never specified what conditional formatting colors you wanted
    Last edited by protonLeah; 06-13-2019 at 08:58 PM.

  21. #21
    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

    NO NO NO. I do get the correct results.
    For instance FL(2) 10s is 0.5s Flash, 1s eclipse, 0.5s Flash, 8s eclipse total 1s on and 9s off. 1 devided by 10 = 0.1 which is 10% the last time I was in High School 56 years ago.

    I have the colors I want. I use these colors so I can see the shading using a Laser Printer.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

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

    So is there still an issue to be resolved here? I have looked at your spreadsheet and I cannot see what is wrong with the results you have.

  23. #23
    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

    There is no issue with my Workbook. Others seem I am doing it wrong, but it works for me so why fix it.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

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

    In that case, please would you mark the thread as solved?

    Select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Why this match/Index formula works in Excel 2007 but not in Excel 2002??
    By EvelynLoh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2017, 09:53 PM
  2. Replies: 6
    Last Post: 04-24-2014, 08:41 AM
  3. Replies: 2
    Last Post: 05-24-2013, 03:08 PM
  4. Excel 2007 Formula files not opening properly in Excel 2010 #Name
    By irbalaji in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-26-2013, 02:13 AM
  5. Replies: 4
    Last Post: 11-05-2012, 07:15 AM
  6. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  7. Replies: 2
    Last Post: 04-29-2009, 01:57 AM

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