Average between last and next value, dynamic range?

[pekoni]

Hi,

I have a very basic question.

I get data from lab that I use for my excel sheet. Sometimes there is blanks between values and those gaps might be days or even weeks. The interval between values is not regular. I would like to have averages between the latest provided value and first given value before that. I think I need to use some sort of dynamic range?

Regards

 

[Special-K99]

Discussion (and possible solution) here (Im too lazy to read it all)

http://www.mrexcel.com/forum/showthread.php?t=77094

 

[pekoni]

Discussion (and possible solution) here (Im too lazy to read it all)

http://www.mrexcel.com/forum/showthread.php?t=77094

thanks for a quick reply. That was not exactly what I was looking for.
I have a column with numbers let's say {791, 785, " ", " ", " ", 799, " ", 801, 755, " "," "," "," "," ", 801}.
So I would calculate first average between 785 and 799 and copy value to the empty cells. Then same again between 799 to 801 and 755 to 801. So instead of ignoring blanks I would fill them with moving average.
So I been thinking statement =IF(C2= " ";AVERAGE(moving range);C2)

 

[gsbelbin]

thanks for a quick reply. That was not exactly what I was looking for.
I have a column with numbers let's say {791, 785, " ", " ", " ", 799, " ", 801, 755, " "," "," "," "," ", 801}.
So I would calculate first average between 785 and 799 and copy value to the empty cells. Then same again between 799 to 801 and 755 to 801. So instead of ignoring blanks I would fill them with moving average.
So I been thinking statement =IF(C2= " ";AVERAGE(moving range);C2)

I can't see how to do this with a formula but it could be done with a gap filling routine which you could run from a button. The code below should do the trick

Sub GapFill()
    Dim cell As Range
    Dim llngCount As Long
    Dim ldblSum As Double
    Dim ldblAverage As Double
 
    llngCount = 0
    ldblSum = 0#
    ldblAverage = 0#
 
    For Each cell In Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)
            If cell.Value <> "" Then 'Non-blank cell
                llngCount = llngCount + 1
                ldblSum = ldblSum + cell.Value
                ldblAverage = ldblSum / llngCount
            Else    'Blank cell
                cell.Value = ldblAverage
                llngCount = 0
                ldblSum = 0
            End If
    Next cell
End Sub

 

[pekoni]

Thanks for the reply.

GapFill worked but I can't produce correct averages for column. I put some sample data to 4shared (sorry about location, I didn't had clue of better spot).
http://www.4shared.com/document/ISPy10fi/sample_data.html
Maybe it will make more sense then.

 

[Sandeep Warrier]

......
So I been thinking statement =IF(C2= " ";AVERAGE(moving range);C2)

Do you mean something like

Excel Workbook
	A	B
1	100
2	450
3		375
4	300
5	400
6	500
7	600
8		650
9		650
10		650
11	700
12	800
13	900
14	1000
15		1050
16		1050
17		1050
18		1050
19		1050
20		1050
21	1100
22	1200
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Aladin Akyurek]

...
I have a column with numbers let's say {791, 785, " ", " ", " ", 799, " ", 801, 755, " "," "," "," "," ", 801}.
So I would calculate first average between 785 and 799 and copy value to the empty cells. Then same again between 799 to 801 and 755 to 801. So instead of ignoring blanks I would fill them with moving average.
So I been thinking statement =IF(C2= " ";AVERAGE(moving range);C2)

Consider...

<TABLE style="WIDTH: 227pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=302><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8476" width=238><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 179pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=238>Avg Moving Last and Before Last</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>791</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>785</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>788</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>799</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>801</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>800</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>755</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>801</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>778</TD></TR></TBODY></TABLE>

B2, just enter and copy down:

=IF(ISNUMBER(A2),IF(MOD(COUNT($A$1:A2),2)=0,
   AVERAGE(LOOKUP(9.99999999999999E+307,$A$1:A1),A2),""),"")

 

[gsbelbin]

Thanks for the reply.

GapFill worked but I can't produce correct averages for column. I put some sample data to 4shared (sorry about location, I didn't had clue of better spot).
http://www.4shared.com/document/ISPy10fi/sample_data.html
Maybe it will make more sense then.

I can't access that site at work but I'll have a look tonight. When it finds a blank cell the gap fill routine takes the average of the preceding group of non-blank cells. Is that what you wanted?

 

[pekoni]

Thanks to all, I tried Sandeep solution and it did the trick with averages. I think there is no quick and easy solution to put them to the same column so I will make an another sheet for data combining.

 

[Weaver]

With Sandeep's solution, you could always swap the "" at the end of the formula to A1 (or whatever cell reference) then at least the column with the formula would have a full set of data.