Excel formula help? Can this be done

[flamingelephant]

I want to make an excel formula that will let me calculate rolling averages using thirty numbers but if one of the cells is empty it will skip that or those cells but still calculate the average using the last thirty cells that have numbers in them. This could mean that some times it takes the average from thirty cells but others maybe look at fifty cells since twenty would be empty. If I was writing a program I could use a counter to count thirty cells with numbers in them and use then until you get thirty values to use. How could you get this to work in excel?

Thanks

 

[Venom20]

I never use excel, but would it be possible to use an if statement? What are the values of theses empty cells? are they 0 or null?

 

[flamingelephant]

Empty cells are null

 

[Dulanic]

Ignore, I'm fixing...

 

[Dulanic]

This is doing it with a helper column... I know it can be done with a array formula without the helper column, but I am having trouble making it work. It will be a bit complicated using OFFSET & MATCH most likely.

https://skydrive.live.com/#!/view.aspx?cid=3F258AE45F219D1F&resid=3F258AE45F219D1F%212393

Basically use a helper column with =COUNTIF($A$1:A1,"<>") copied down (if data is in A) and then use a averageif formula. =AVERAGEIF(B1:B100,"<=30",A1:A100)

 

[postmark]

This is doing it with a helper column... I know it can be done with a array formula without the helper column, but I am having trouble making it work. It will be a bit complicated using OFFSET & MATCH most likely.

https://skydrive.live.com/#!/view.aspx?cid=3F258AE45F219D1F&resid=3F258AE45F219D1F%212393

Basically use a helper column with =COUNTIF($A$1:A1,"<>") copied down (if data is in A) and then use a averageif formula. =AVERAGEIF(B1:B100,"<=30",A1:A100)

Or you can do it with a macro and not have to do the helper column.

This will look through column A starting with cell A1 and go until A1000 or until it finds 30 values that are not blank. It will then put the result in cell B1.

Sub Macro10()

'
' Macro10 Macro
'

'
    Dim i As Integer
    Dim count As Integer
    Dim RunningSum As Double
         
    count = 0
    RunningSum = 0
        
    For i = 1 To 1000
         
        Cells(i, 1).Select
        
        If Cells(i, 1).Value <> "" Then
            
            count = count + 1
            RunningSum = RunningSum + Cells(i, 1).Value
            
            If count = 30 Then
                Exit For
            End If
        
        End If
    
    Next i

    Cells(1, 2).Select
    Cells(1, 2) = RunningSum / count
    
End Sub

 

[Dulanic]

It can be done with a formula too without a helper column, just took me a bit to do it. This won't need to be run every time.

BTW, I am a fan of macros but this will just prevent that issue of forgetting to run it for example.

https://skydrive.live.com/#!/view.aspx?cid=3F258AE45F219D1F&resid=3F258AE45F219D1F&#37;212394

{=AVERAGE(IF(OFFSET(A1,MATCH(TRUE,INDEX(ISNUMBER(A1:A100),0),0)-1,0,SMALL(IF(A1:A100<>"",ROW(A1:A100),""),30))<>0,OFFSET(A1,MATCH(TRUE,INDEX(ISNUMBER(A1:A100),0),0)-1,0,SMALL(IF(A1:A100<>"",ROW(A1:A100),""),30))))}

Entered as a array formula.

 

[postmark]

It can be done with a formula too without a helper column, just took me a bit to do it. This won't need to be run every time.

BTW, I am a fan of macros but this will just prevent that issue of forgetting to run it for example.

https://skydrive.live.com/#!/view.aspx?cid=3F258AE45F219D1F&resid=3F258AE45F219D1F%212394

{=AVERAGE(IF(OFFSET(A1,MATCH(TRUE,INDEX(ISNUMBER(A1:A100),0),0)-1,0,SMALL(IF(A1:A100<>"",ROW(A1:A100),""),30))<>0,OFFSET(A1,MATCH(TRUE,INDEX(ISNUMBER(A1:A100),0),0)-1,0,SMALL(IF(A1:A100<>"",ROW(A1:A100),""),30))))}

Entered as a array formula.

Impressive

Edit:
Because I'm a big macro guy, I will also show you how to have the macro run automatically if anything in column A changes, that way it sort of acts like a function.

Right click on the worksheet you're on (say Sheet1) and click view code.
On the code window, double click on the Sheet1(Sheet1) in the top left window.
Then on the right window above the blank text area, you should see 2 drop downs. In the first one, select Worksheet and the second one select Change.

This should give you a blank Private Sub Worksheet_Change(ByVal Target As Range)

Make the function look like this (where Macro10 is the name of the macro you created):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
    Run "Macro10"
End If
    
End Sub

 

[Dulanic]

Impressive

Thanks, I won't take 100&#37; credit, I did have to pull in some quick help on the SMALL part to identify the 30th non blank row.

Edit: Oh and my main reason for avoiding things like that is when others use files. I can't count the number of times I get an email ohhh this doesn't work! All because they ignored the enable macros button up top When half your life is spent in excel you quickly find what "stumps" computer illiterate people.

 

[flamingelephant]

Wow, thanks...

Question though:
I'm assuming the equation works from the top down to get the 30 values to average (look at A1, if its a number then include it in the 30 and go onto A2 and repeat until you get 30 values to average). Is there a way to get it to work backwards?

I get daily results and need a 30-day rolling average that excludes nulls. Each day you should get a new value that needs it and the previous 29 that are not nulls averaged. Does this make sense? Or possibly the easiest solution might be to reverse the order of the data (i.e. put the newest values on the top of the spreadsheet instead of at the bottom?

 

[Dulanic]

Doing so backwards is actually easier:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ISNUMBER(A1:A100)*ROW(A1:A100),30)))

Also an array formula. I added it to the linked file above.

 

[flamingelephant]

thanks, much appreciated