Average of several columns grouped... and a conditional concantenate

[alderman]

Two questions:

1. Is there an equation to determine the average of several combined cells?

In the sample table below I would like to get the average of rows 4-7, but as a group 23, 23, 22, 24, 24 for an average of 23.2 rather than the average of the cells $D$4:$F$8 which is 14.5

2. The next thing I would like to do is concatenate in column A the column headers... for example in A2 and A3 I would like to see C, but in A5 and A6 I would like to see D/E and if there were more than 2 coplumns this could be extended such that the result woudl be D/E/F etc.

Thanks for any help on this.

	A	B	C	D	E	F	G	H	I	J	K
1		Avg
2			21
3			22
4				23
5				16	7
6				15	7
7					16	8
8						24
9							23	6
10								21	6
11									20	8
12										28

[Simi]

for your 1st problem you could do this:
=SUM(D4:F8)/ROWS(D4:F8)

simply sums the area then divides by the number of rows of data.

I'm not sure how to help you on the 2nd problem, not with just a formula anyway.
in cell A5 would you like the data to show up as 167, or 16/7?

[alderman]

Thanks Simi,

The data is a little more complicated as there may be empty rows of data and the numbers may change over time. The only way I can think of doing this is to have another column that adds each select row (i.e. SUM(D48)) and then find the average by using the countif equation for any values greater than zero. I was hoping for a more elegant solution and no hidden columns.


As for the second, it is unlcear as there are no headers, but if I use the column labels as headers I would be looking for a return in A5 in the example above to be D/E, but if a number was entered in F5 then I would want to see D/E/F in A5.

Hope this clarifies.

[Simi]

Do you know how to use the VBA?
I don't think it would be too difficult to create a macro to get the column letters in the format you show.
you can easily get the first and last column that has data per row. will there ever be data on the same line with blanks between them? d5, e5, g5? where f5 is blank?

and adding the extra column to sum the row is what my first suggestion was going to be, again if you use some code, you could do those sum calculations in the code and avoid having the hidden column.

Do you have a large set of data to test this on with your variances of blank rows/cells that you could upload?

[alderman]

Thanks Simi,

I have never created a macro, but it looks like that is the next direction I need to go. I have been getting that answer a great deal recently.... "a macro could do that"

In my data sets there would never be a blank cellas you describe above. Doe sthat make it manageable without a macro?

[Simi]

not that I know of but it makes it easier with a macro .

[alderman]

Thanks for the suggestions!

[alderman]

Hi Simi,

I ended up using a heap of nested IF statements and counta criteria to make it work.... it was a brute force method that works, but has no elegance to it.