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

Question

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?

Thanks in advance.

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

Answer 1

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.

Answer 2

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