Excel 2003 Average If offset match dynamic range arghhh

DG06 · Jul 5, 2011

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 · Jul 5, 2011

Welcome to the MrExcel board!

1. Are the area numbers in column A in numerical order?

2. Do the area numbers in column A always increment by just 1?

Noting your post-editing comment, perhaps you could investigate the 3 methods suggested in my signature block for posting small screen shots. If you do, test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

Misca · Jul 5, 2011

If VBA is an option try using something like:

Code:

Dim c As Range
Dim Rng As Range
With Sheets(1)
    For Each c In Intersect(.Range("A:A"), .UsedRange, .Rows("2:" & .Rows.Count))
        If c.Value <> "" Then
            Set Rng = .Range(c.Offset(0, 1), c.Offset(0, 3).End(xlDown))
            ActiveWorkbook.Names.Add Name:="Area" & c.Value, RefersTo:=Rng
        End If
    Next c
End With

This piece of code should put the postcodes and numbers into named ranges.

The code expects all the named ranges to be surrounded by empty row. If this is the case with your data feed it should work just fine but if the row only appears to be empty you'll have to tweak the code a little bit to make it work.

Once you have the named ranges the rest is quite straight forward:
=AVERAGE(OFFSET(INDIRECT("Area"&$H2),0,1)) should work where H2 houses the Area Number.

Peter_SSs · Jul 5, 2011

If using vba, I think I would try to avoid the named ranges and the volatile OFFSET & INDIRECT functions.

Assuming the 'Area Report' Sheet has a layout like below and the area of interest is to be placed in cell A2, then I would consider this Worksheet_Change code for the 'Area Report' sheet.

I have also assumed that there is at least one blank row between each area in the 'Data' sheet.

Excel Workbook

A

B

C

1

Area

Average 1

Average 2

2

20101

3.327

5.312

Area Report

To implement ..

1. Right click the 'Area Report' sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering an area value in to cell A2 of the 'Area Report' sheet.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim rArea As Range, rNumber1 As Range If Target.Address = "$A$2" Then Application.EnableEvents = False If Target.Value = "" Then Target.Offset(, 1).Resize(, 2).ClearContents Else With Sheets("Data") Set rArea = .Columns("A").Find(What:=Target.Value, _ LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False) Set rNumber1 = Intersect(.Columns("C"), rArea.CurrentRegion) Target.Offset(, 1).Formula = _ "=AVERAGE('" & .Name & "'!" & rNumber1.Address & ")" Target.Offset(, 2).Formula = _ "=AVERAGE('" & .Name & "'!" & rNumber1.Offset(, 1).Address & ")" End With End If Application.EnableEvents = True End If End Sub

DG06 · Jul 5, 2011

Ok so thank you everyone.... VB is an option in fact within other sections of this I have various codes in place to re-fresh autofilters etc...

That being said whilst the code provided works for a single line there is going to be a dynamic range in the front page depending on a state / region input.

The numbers in column A are sequential however not necessarily increasing by 1.
Taking your advice I will attempt a HTML version here..

Data Sheet

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Area</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">BSB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Site</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sum of Total Forecasted Service FTE</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Scheduled Service Resources</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Total Forecasted Sales FTE</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Scheduled Sales Resources</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Total Branch Forecasted FTE</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Total Scheduled FTE</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">Sum of Branch NET FTE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">Sum of Vacancies</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20101</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2401</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">North Sydney </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.58</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.54</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.79</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.37</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">11.54</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">3.17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2057</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">255 George St NAB House </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4.61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.63</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.18</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">9.79</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">12.63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">2.83</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.00</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2001</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">345 George Street </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4.34</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.30</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.11</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.77</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">10.45</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">14.07</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">3.63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2067</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pitt & Hunter Streets </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.79</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.53</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.86</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.85</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.66</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">11.38</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">2.72</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.84</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2053</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Law Courts </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.63</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.14</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.83</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.46</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.69</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.00</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2024</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">World Square </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.45</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.29</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.51</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">9.96</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">10.29</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.33</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2080</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pitt & Bathurst Streets </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.17</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.51</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.96</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.13</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.51</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.38</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2052</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">134 Liverpool Street </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.45</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.14</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.94</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.48</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.39</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.62</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.78</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2604</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Haymarket </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.38</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.38</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.27</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.81</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.66</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.19</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">2.53</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2310</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">130 Pitt Street </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.87</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.66</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.22</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.09</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">9.66</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">6.57</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.60</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">33.28</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">53.12</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">42.68</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">45.91</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">75.96</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">99.03</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">23.07</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">5.43</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20102</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2201</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Chatswood </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4.76</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.41</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.12</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.14</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">10.89</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">12.55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.66</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.41</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2352</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Manly </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.44</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.88</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.47</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.91</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.88</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.98</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.14</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2445</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">St Ives </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.51</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.18</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.48</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.99</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.18</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2146</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Brookvale </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.92</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.72</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.52</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.72</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.20</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2294</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mona Vale </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.10</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.21</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.92</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.02</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.21</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2116</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Avalon Beach </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.86</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.95</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.17</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.03</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.95</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.08</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2212</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Crows Nest </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.07</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.79</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.59</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.66</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.79</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2183</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Dee Why </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.47</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.47</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.84</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.48</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.32</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.95</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2302</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Neutral Bay </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.16</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.95</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.16</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.95</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.22</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2289</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Gordon </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.23</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.92</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.34</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.57</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.92</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2132</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Belrose Glenrose Centre </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.10</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.80</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.14</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.24</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.80</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.44</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2309</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hornsby </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4.57</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.09</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.35</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.38</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.92</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.48</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-1.44</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2299</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mosman Junction </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.32</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.85</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.37</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.69</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.85</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.85</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">36.53</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">47.22</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">38.40</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">32.00</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">74.93</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">79.22</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">4.29</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">1.39</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20104</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2918</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Windsor </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.63</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.03</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.46</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">7.09</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.03</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.94</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2155</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Castle Hill </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.79</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.12</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.41</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.21</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.12</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">1.91</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2443</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ryde </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.96</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.37</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.35</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.31</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.37</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.94</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2240</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Dural </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.35</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.25</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.00</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.39</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2365</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Seven Hills </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.50</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.87</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.90</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.51</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.40</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.38</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.02</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.37</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2207</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Gladesville </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.26</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.53</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.39</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.65</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.53</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2254</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lane Cove </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.40</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.73</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.55</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.95</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.73</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.78</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2808</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Rouse Hill </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.69</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.73</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.99</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.68</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.73</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.05</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2204</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Eastwood </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.05</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.23</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.11</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.16</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.23</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">0.07</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.72</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2167</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Norwest </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.59</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.89</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.25</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.53</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.84</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.42</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.42</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2345</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kings Park </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.29</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.60</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1.53</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">0.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.82</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.60</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.22</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.53</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2135</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Baulkham Hills </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.36</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.14</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.24</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">0.85</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.60</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.99</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-1.61</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2186</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Carlingford </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.31</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.74</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.44</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.75</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.74</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.40</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2241</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Macquarie University </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.27</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.31</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.25</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">0.93</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.53</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.24</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.28</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.88</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2344</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Macquarie Shopping Centre </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.59</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.19</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4.57</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">2.87</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.16</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">8.06</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">-0.10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.78</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">38.06</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">50.48</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">39.70</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">27.69</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">77.76</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">78.16</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">0.41</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF99;;">4.68</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20106</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2637</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hamilton </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.18</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.29</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">3.41</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5.46</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">6.59</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">10.75</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFCC;;">4.16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1.27</td></tr></tbody></table>Data Pivot as Value 

And now the report page in raw form...

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Selection</td><td style="background-color: #FFFF00;;">NSW/ACT</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Average by Area</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Forecasted Service FTE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Scheduled Service Resources</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Forecasted Sales FTE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Scheduled Sales Resources</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total Branch Forecasted FTE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total Scheduled FTE</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFCC;;">Branch NET FTE</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vacancies</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFCC;;">TRUE NET FTE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20101</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20102</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20104</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20106</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20107</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20108</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20110</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20802</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20803</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20804</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20805</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20806</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20807</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20808</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table>LAM Average 

The Cell in B2 is a data Validation box that when selected populated the table with the Area Numbers that reside in that State... I then want the averages for all BSB's in that area to populate the table...

Hope this works being a n00b and all....

Thanks so much for the help

DG

Peter_SSs · Jul 7, 2011

That looks to me like you are just trying to extract the yellow rows from the first sheet into the second? If that is so, you could try (in copies of your workbook)

A. Formula method.
Formula in C5 is copied across and down.
I've only shown a part of the sheet.

Excel Workbook

B

C

D

E

F

G

H

4

Forecasted Service FTE

Scheduled Service Resources

Forecasted Sales FTE

Scheduled Sales Resources

Total Branch Forecasted FTE

Total Scheduled FTE

5

20101

33.28

53.12

42.68

45.91

75.96

99.03

6

20102

36.53

47.22

38.4

32

74.93

79.22

7

20104

38.06

50.48

39.7

27.69

77.76

78.16

LAM Average

B. Macro method

LAM Average sheet Module gets this code:

 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then ExtractAverages End If End Sub 

and in a general Module:

 Sub ExtractAverages() Dim aLAM, aPT Dim wsPT As Worksheet, wsLAM As Worksheet Dim cols As Long, rws As Long, i As Long, j As Long Dim PTrws As Long, k As Long Dim aName As String Application.EnableEvents = False On Error GoTo PreExit Set wsPT = Sheets("Data Pivot as Value") Set wsLAM = Sheets("LAM Average") With wsLAM cols = .Cells(4, .Columns.Count).End(xlToLeft).Column - 2 aLAM = .Range("B5", .Range("B" & .Rows.Count).End(xlUp)).Resize(, cols + 1).Value rws = UBound(aLAM, 1) .UsedRange.Offset(4, 2).ClearContents End With aPT = wsPT.Range("A8").CurrentRegion.Resize(, cols + 3).Value PTrws = UBound(aPT, 1) For i = 1 To rws aName = aLAM(i, 1) k = 1 Do While aPT(k, 1) <> aName And k <= PTrws k = k + 1 Loop If k <= PTrws Then Do While aPT(k, 2) <> "" k = k + 1 Loop For j = 1 To cols aLAM(i, j + 1) = aPT(k, j + 3) Next j End If Next i wsLAM.Range("B5").Resize(rws, cols + 1).Value = aLAM PreExit: Application.EnableEvents = True End Sub

Peter_SSs · Jul 7, 2011

Forgot to mention that if using the formula, make sure where I have 1000 in several places in the formula that that number is larger than the last row you are likely to have on the 'Data Pivot as Value' sheet.

DG06 · Jul 7, 2011

Wow that's rad! Thanks so much...

That being said the yellow numbers are totals, I actually need to compute the average without modifying the data table to include averages...

At the moment i'm exploring the if statement you provided to see if i can't add a divide by count...

Thanks again.. Nearly there...

Peter_SSs · Jul 7, 2011

Sorry, I don't know what made think the yellow lines were what you wanted - clearly not averages.

Looks like you are, investigating the formula approach. Such an approach would be much simpler if we could use a couple of helper column on the 'LAM Average' sheet. I've used columns M:N but they could be any columns and they could be hidden once you've copied the formulas down.

Formula in C5 again copied across and down.
Formulas in M5 and N5 just copied down.
Once again I haven't shown the whole sheet - just hidden some columns to make the screen shot a bit smaller.

Excel Workbook

B

C

D

E

F

L

M

N

4

Forecasted Service FTE

Scheduled Service Resources

Forecasted Sales FTE

Scheduled Sales Resources

Start

End

5

20101

3.327

5.312

4.267

4.591

9

18

6

20102

2.808461538

3.632307692

2.953846154

2.461538462

20

32

7

20104

2.536

3.365333333

2.646

1.846

34

48

8

20106

3.18

5.29

3.41

5.46

50

LAM Average

DG06 · Jul 7, 2011

You're a dead set legend... I've opted to use the first formula solution to provide a totals report, under this i have an averages report which i use a hidden column to reference a names sheet i have that has a counta of the branches in each LAM... Offset Match to get this into the hidden column and then its a basic divide the total by this number to get average...

Thanks so much for you help!!!! Brilliant!

Cheers

DG

Excel 2003 Average If offset match dynamic range arghhh

DG06

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Misca

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

DG06

New Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

DG06

New Member

Peter_SSs

MrExcel MVP, Moderator

DG06

New Member

Similar threads

Forum statistics

Share this page

Excel 2003 Average If offset match dynamic range arghhh

New Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock