Alternate Line referances

[eugene7]

Hi,
I need to build a column of data with each alternate cell referencing a sequential list, with the odd cells averaging the data in the alternate cells thus...

B1 - references - A1
B2 - averages B1 & B3
B3 - references A2
B4 - averages B3 & B5
B5 - references A3
....

The problem is the (B) list has to be 35,940 cells long!

Is there a way to automate the writing of this list such that each alternate cell in list B references the next sequential cell in list A???

 

[Legacy 98055]

I tried to use Excel's fill down to repeat the pattern but I could not get it to work. Place this macro in your worksheet class and run it.

Assumes, as you stated in your example, range B1:B35940.

1130920.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Temp()
       <font color="#0000A0">Dim</font> a(1 <font color="#0000A0">To</font> 35940, 1 <font color="#0000A0">To</font> 1)
       <font color="#0000A0">Dim</font> x <font color="#0000A0">As</font> Long, y <font color="#0000A0">As</font> Long, r <font color="#0000A0">As</font> Range

       Application.ScreenUpdating = <font color="#0000A0">False</font>
       Me.EnableCalculation = <font color="#0000A0">False</font>

       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> r <font color="#0000A0">In</font> Range("B1:B35940")
           y = y + 1
           <font color="#0000A0">If</font> y <font color="#0000A0">And</font> 1 <font color="#0000A0">Then</font>
               x = x + 1
               a(y, 1) = "=A" & CStr(x)
           <font color="#0000A0">Else</font>
               a(y, 1) = "=AVERAGE(B" & CStr(y - 1) & ",B" & CStr(y + 1) & ")"
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">Next</font>

       Range("B1:B35940") = a
       Me.EnableCalculation = <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("930200614012984").value=document.all("930200614012984").value.replace(/<br \/>\s\s/g,"");document.all("930200614012984").value=document.all("930200614012984").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("930200614012984").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="930200614012984" wrap="virtual">
Sub Temp()
Dim a(1 To 35940, 1 To 1)
Dim x As Long, y As Long, r As Range

Application.ScreenUpdating = False
Me.EnableCalculation = False

For Each r In Range("B1:B35940")
y = y + 1
If y And 1 Then
x = x + 1
a(y, 1) = "=A" & CStr(x)
Else
a(y, 1) = "=AVERAGE(B" & CStr(y - 1) & ",B" & CStr(y + 1) & ")"
End If
Next

Range("B1:B35940") = a
Me.EnableCalculation = True
End Sub</textarea>

1130920.zip

 

[pgc01]

Hi Steve

A formula solution. In B1:

=AVERAGE(OFFSET($A$1,-1+CEILING(ROW()/2,1),0,2-MOD(ROW(),2),1))

Copy down

Hope this helps
PGC