Using address to get the range of values for average()

[Wyrmtong]

I've got a slightly annoying problem. I'm trying to input the range for my average() function by using Address(). But when i do, it gives me the error "A value used in the formula is of the wrong data type" which is obviously very annoying. The formula is suppose to grab all viable values in a range if and only if there is no value in the cell it is referencing to, then calculate the average of all the values which belong to the same group.

I'm not very good at explaining... sorry. But at least i can give you the formula to look at and the test data :D

group	AH	AH	BH	BH	BH	BH	BH	BL	BL	CH	CH	CH
site	RS40	RS46	RS13	RS16	RS21	RS23	RS56	RS15	RS54	RA01	RS35	RS45
Article
27420	64		29	27	82	11		68	27	31		56
138209	486	10	269	108	261	66		271	99	124	13	223
27859	153	14	96	41	226	22		105	55	92	18	112
27896	508		239	67	458	57		351	65	87		197
38119	319	13	228	67	454	32		165	79	132	26	225
138210	128	5	62	26	150	27		120	23	41	8	96
135202	169	5	192	14	157	24		62	34	32	16	160
135203	170	13	166	23	196	15		62	73	48	11	166
135201	91	9	249	33	103	10		57	41	34	10	132
135205	52	2	91	17	76	8		48	24	14	2	26
135204	75	14	111	13	88	5		80	21	16		57

<tbody>
</tbody>

=AVERAGE(ADDRESS(ROW()-5,MATCH(D$1,$1:$1,0)) & ":" & ADDRESS(ROW()-5,MATCH(D$1,$1:$1,0)+COUNTIF($B$1:$N$1,"="&D$1)-1))

THanks for any help that is given

 

[Andrew Poulsom]

One way:

=AVERAGE(INDEX(B4:M4,,MATCH(D1,B1:M1,FALSE)):INDEX(B14:M14,,MATCH(D1,B1:M1,FALSE)+COUNTIF(B1:M1,D1)-1))

 

[Wyrmtong]

Thanks for the quick reply Andrew.

I had thought of using index,the problem is that I duplicate the range. And in the second range i either need to copy the value as is ( when the cell is not empty) or i need to average across the group for one specific article.

EG: for article 27896, there's values in group BH ( columns D - G ). But there is no value in column H. So i need the formula to fill cell (H7) with the average of the other 4 cells in that group that actually has values.

Sorry if my explanation is crappy... i usually struggle with explaining stuff...

 

[Andrew Poulsom]

In H4 copied down:

=AVERAGE(INDEX(B4:G4,,MATCH(D$1,B$1:G$1,FALSE)):INDEX(B4:G4,,MATCH(D$1,B$1:G$1,FALSE)+COUNTIF(B$1:G$1,D$1)-1))

 

[Wyrmtong]

Thanks for the formula Andrew. I adjusted it slightly, but now it works like a charm. Didn't know you could use index as a cell reference for a range, which is why i avoided it.

Anywho, thanks again.