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

Wyrmtong

New Member
Joined
Jul 11, 2012
Messages
3
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

groupAHAHBHBHBHBHBHBLBLCHCHCH
siteRS40RS46RS13RS16RS21RS23RS56RS15RS54RA01RS35RS45
Article
27420642927821168273156
13820948610269108261662719912413223
2785915314964122622105559218112
2789650823967458573516587197
381193191322867454321657913226225
13821012856226150271202341896
1352021695192141572462343216160
13520317013166231961562734811166
135201919249331031057413410132
1352055229117768482414226
13520475141111388580211657

<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 :)
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 11, 2012
Messages
3
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 11, 2012
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,988
Members
414,418
Latest member
mightyMagnus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top