# Help with Excel 2007 formula - Divide SUM of Cells

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

2. ## Re: Help with Excel 2007 formula

Welcome to the board.

Thanks.

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

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

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

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

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

Originally Posted by protonLeah
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.

7. ## 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. ## 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. ## Re: Help with Excel 2007 formula - Divide SUM of Cells

Look at the screenshot.

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. ## 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. ## 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. ## 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. ## Re: Help with Excel 2007 formula - Divide SUM of Cells

Are calculations set to Manual?

16. ## 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. ## Re: Help with Excel 2007 formula - Divide SUM of Cells

Originally Posted by Pepe Le Mokko
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. ## 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% ..."

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

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

21. ## 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. ## 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. ## 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. ## Re: Help with Excel 2007 formula - Divide SUM of Cells

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

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