# How to divide the sum of cells by 7 and then transpose them in Excel

I need to take a vertical row of numbers in excel and add them all and divide by seven. I then need to transpose the result into 7 equal rows (one for each day of the week.)

I have tried the following in excel =TRANSPOSE("=SUM(D1:D4)/7") but afterwards I realised that it would probably only transpose the number into one cell rather than 7, and even that didn't work!

So for example I'd like to do the following, in this example I'm going to use 4 rows instead of 7. but the same thing would apply except to 7 rows.

``````6
5
4
3
18 (=SUM(D1:D4))
``````

then =SUM(D1:D4)/5 which would equal 3.6

I would then like to be able to transpose this result to 4 horizontal rows so it would look like this

``````3.6 3.6 3.6 3.6
``````

I thought I would be able to use the following function =TRANSPOSE("=SUM(D1:D4)/7")

But all I get in the field for the result is the following =SUM(D1:D4)/5, and that only appears in the first horizontal field. Not the whole 4 of them

Any ideas on how I could transpose this? And how that function is written?

****Edit I'm going to put this in here as well as in one of the comments so that it's seen Thanks guys, While the answers have been extremely useful to me, the spec has since changed. They also now want the cost codes and descriptions to correlate horizontally aswell as assigning each horizontal cell a date which would probably involve writing a pretty long macro. I'm not very experienced with this at all and i'm struggling with basic functions as is, and seeing as there is only 8 weeks worth of timesheets to be transferred to the new system, they figure its easier to just reference the old database until december when the company holiday year is up. Thank you for the responses**

## migrated from stackoverflow.comSep 28 '12 at 18:10

This question came from our site for professional and enthusiast programmers.

• It's always a good idea to start with the documentation (RTFM) or search the web---I admit, MS help or documentation is often bad, still Excel help tells "TRANSPOSE function must be entered as an array formula ... are entered by pressing CTRL+SHIFT+ENTER.)" – thoku Sep 27 '12 at 17:40
• Thanks guys, While the answers have been extremely useful to me, the spec has since changed. They also now want the cost codes and descriptions to correlate horizontally aswell as assigning each horizontal cell a date which would probably involve writing a pretty long macro. I'm not very experienced with this at all and i'm struggling with basic functions as is, and seeing as there is only 8 weeks worth of timesheets to be transferred to the new system, they figure its easier to just reference the old database until december when the company holiday year is up. Thank you for the responses – user1702830 Sep 28 '12 at 10:56

You don't have to use the `TRANSPOSE` function to do this, just use an `array formula`.

Assuming you have the following:

``````ColA
6
5
4
3
``````

If you highlight cells `A5:G5` (with `A5` being the active cell), then you type the formula `=sum(A1:A4)/5` and press `ctrl+shft+enter`, it will produce this:

``````ColA ColB ColC ColD ColE ColF ColG
6
5
4
3
3.6  3.6  3.6  3.6  3.6  3.6  3.6
``````

Changing any of the values in cells `A1:A4` will update all the formula totals.

• Can I do this with a cell thats already in use, as in swap A5:G5 to A1:G5 and overwrite the value that is A1? Also when I tried to change the value I got an error messages saying that I could not change a value in an array? Also is there a way that I could actually have a typed function which would allow it to be done automatically rather than pressing ctrl+Shift+enter? – user1702830 Sep 27 '12 at 15:58
• I have no problem pressing those buttons together only i'm actually passing on the function to an administrator to do it as she's trying to fix incorrect timesheets in order to prepare the excel database to be exported within the contstraints of the new timesheet software they'll be using. – user1702830 Sep 27 '12 at 16:00
• Whatever cells you have highlighted when you enter the formula and press `ctrl+shift+enter` will be overwritten with the `array formula`. Be careful not to create a circular reference however... And remember that once you create an array formula, the only way to edit it is to select all the cells again, edit the formula and then press `ctrl+shift+enter` again. – James L. Sep 27 '12 at 16:05
• By `circular reference`, I mean you can't use any of the highlighted cells in the formula, or the formula will reference its own cells. That doesn't work. – James L. Sep 27 '12 at 16:06

I'd suggest that the simplest way is to just use this formula in the first cell

`=SUM(\$D1:\$D4)/7`

Then copy across to 6 more cells

If you want a single function the this should do it

`=SUM(\$D1:\$D4)/{7,7,7,7,7,7,7}`

"array entered" in a 7 cell horizontal range

• +1, clever trick to use an array in the denominator. Didn't know you could use different values in the denominator like that (e.g., `{7,3,5,1,6,2,8}`). – James L. Sep 27 '12 at 15:58