Average between last and next value, dynamic range?

pekoni

New Member
Joined
Oct 30, 2008
Messages
6
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

pekoni

New Member
Joined
Oct 30, 2008
Messages
6
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

Active Member
Joined
Aug 29, 2008
Messages
336
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

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

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
......
So I been thinking statement =IF(C2= " ";AVERAGE(moving range);C2)
Do you mean something like
Excel Workbook
AB
1100
2450
3375
4300
5400
6500
7600
8650
9650
10650
11700
12800
13900
141000
151050
161050
171050
181050
191050
201050
211100
221200
Sheet1
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

...
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:
Code:
=IF(ISNUMBER(A2),IF(MOD(COUNT($A$1:A2),2)=0,
   AVERAGE(LOOKUP(9.99999999999999E+307,$A$1:A1),A2),""),"")
 

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
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

New Member
Joined
Oct 30, 2008
Messages
6
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,733
Messages
5,513,077
Members
408,937
Latest member
NAGARAJAN_EXCEL

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top