Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Sep 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
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.