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
THanks for any help that is given
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>
Code:
=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