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**
Best Answer
You don't have to use the
TRANSPOSE
function to do this, just use anarray formula
.Assuming you have the following:
If you highlight cells
A5:G5
(withA5
being the active cell), then you type the formula=sum(A1:A4)/5
and pressctrl+shft+enter
, it will produce this:Changing any of the values in cells
A1:A4
will update all the formula totals.