Excel 2003 Average If offset match dynamic range arghhh

[DG06]

Hi All,

Multiple time reader, first time poster...

My challenge, I have a significant data set that I am creating a report on.

the challenge is I want to create an average using firstly the unique area number that only sits in the top left of each area and then average all the postcodes that are in that area.

Here's a sample of the data sheet... We will call this sheet "Data"

A B C D
Area postcode Number Number 2
20101 2401 3.58 4.54
(empty cell)2057 4.61 6.63
(empty cell)2001 4.34 8.30
(empty cell)2067 3.79 6.53
(empty cell)2053 3.63 5.14
(empty cell)2024 3.45 5.29
(empty cell)2080 3.17 3.51
(empty cell)2052 2.45 3.14
(empty cell)2604 2.38 4.38
(empty cell)2310 1.87 5.66

20102 2201 4.76 7.41
(empty cell)2352 2.44 3.88
(empty cell)2445 2.51 3.18
(empty cell)2146 2.92 3.72
(empty cell)2294 3.10 4.21
(empty cell)2116 1.86 1.95
(empty cell)2212 3.07 3.79
(empty cell)2183 2.47 3.47
(empty cell)2302 2.16 2.95
(empty cell)2289 2.23 2.92
(empty cell)2132 2.10 2.80
(empty cell)2309 4.57 4.09
(empty cell)2299 2.32 2.85

So if the user in the report selects 20101 I want to then give an average of all the numbers in all the postcodes following that area.

I am unable to populate the area next to each postcode as this will impact the source data (which is a feed)

I have over 50 areas and over 700 postcodes which are going to be reported on by a state selection.

This is the sheet "Area Report"
where I have put in what the answer should be if returned, I am at a loss as to the best formula to use.

Average Number Average Number 2
20101 =3.33 =5.31
20102 =2.81 =3.63


Any suggestions would be great?

Thanks,

DG

 

[Peter_SSs]

Glad you are getting there.

The totals formulas would also be much simpler with the helper columns. With the layout of my last post and the same formulas in columns M:N, try this in C5 (copied across and down)

=IF($B5="","",INDEX('Data Pivot as Value'!D$1:D$1000,$N5+1))