Extracting result from data

[graham84]

Hello excel geniuses, I have a list of data. For example:

Data
10
20
--
32
--
--
--
25
30
--
40
55

I need a formula to be filled down the side of the list, which ignores the dashed rows and sums back the last 10 numbers, not 10 rows. So the offset range needs to expand back, x times if there's x dashed rows. I cannot delete the dashed rows, they must stay. And the dashed rows could be anywhere in the list.

I did try doing this using the offset function by totaling the number of dashed cells in the range and adding it on the offset rows, however it came unstuck when the cells above were also dashed...

Anyone got a painless solution, I would prefer a formula to vba. Thanks alot

 

[graham84]

this is the formula I came up with, which works until the rows added on are dashed..

=SUM(OFFSET(A1,0,0,(-10+-COUNTIF(OFFSET(A1, 0, 0, -10, 1),"--")),1))


Any help much appreciated

 

[Trouttrap2]

For this to work you have to have at least 10 numbers in your column or you will get #NUM! errors.
CTRL + SHFT +ENTER, not just enter this formula in B1, assuming your data is in A1. Drag down.

=SUM(N(INDIRECT("A"&LARGE(IF(TRUE=ISNUMBER(A$1:A1),ROW(A$1:A1)),{1,2,3,4,5,6,7,8,9,10}))))

 

[graham84]

Hey, Thanks for that. It works....

However, I've changed my mind and decided it would be easier to create a UDF, as I'll be calling the function quite a few times.

Does anyone have an efficient example?

I'm having a go at creating something, but my excel skills are embarassingly rusty.

Public Function SumAndSkip(R As Long, C As Long, P As Long) As Long

    Application.Volatile

    Dim Res As Long, infectedRows As Long

    With Sheets("Sheet1")
 
        infectedRows = Application.WorksheetFunction.CountIf(.Cells(R - P, C).Resize(R, C), "--")
        
        If infectedRows > 0 Then
        
            
        Else
            Res = R
        End If
    
        SumAndSkip = Application.Sum(.Cells(Res - P, C).Resize(R, C))
    
    End With
    
End Function

 

[graham84]

Can anyone check over this code, and see why its not working?
cheers

Public Function SumAndSkip(ER As Long, C As Long, P As Long) As Long

    Application.Volatile

    Dim I As Long
    Dim Res As Long    'Result
    Dim iRows As Long  'Infected Rows
    Dim SR As Long     'Start Row
    
    SR = (ER - (P - 1))    'StartRow = (EndRow-Parameter)

    With Sheets("Sheet1")
    
        iRows = Application.WorksheetFunction.CountIf(.Cells(SR, C).Resize(ER, C), "--")
        
        If iRows > 0 Then
            'loop back
            Do Until SR = 1 Or iRows = 0
                SR = SR - 1
                If Not .Cells(SR, C) = "--" Then
                    iRows = iRows - 1
                End If
            Loop
        End If

        SumAndSkip = Application.Sum(.Cells(SR, C).Resize(ER, C))
    
    End With
    
End Function

 

[graham84]

I've come up with this code, seems to work, and is fairly fast.

Can anyone see any mistakes, or ways to improve the code?

Thanks

Public Function SumSkipDash2(R As Range, N As Long) As Long

    'Application.Volatile

    Dim C As Long  ' Column
    Dim F As Long  ' Number of infected rows
    Dim S As Long  ' Current position
    Dim J As Long  ' Count
    Dim L As Long  ' Length
    
    S = (R.Row - (N - 1))  ' Set cell position
    C = R.Column           ' Set column
    
    With ActiveSheet
        
        L = N   ' set length
    
        'count number of infected rows
        F = Application.WorksheetFunction.CountIf(.Cells(S, C).Resize(L, C), "--")
        
        If F > 0 Then
            J = N - F
            Do Until J = N
                S = S - 1
                If Not .Cells(S, C) = "--" Then J = J + 1
                L = L + 1
            Loop
        End If
        
        SumSkipDash2 = Application.WorksheetFunction.Sum(.Cells(S, C).Resize(L, C))
    End With

End Function

 

[graham84]

Hi Trouttrap, I've been looking at your formula again today, and is it possible for it to be modified so instead of returning the summed result, a position is returned instead?

I can obtain the summed result using an offset...

Thanks

 

[Trouttrap2]

Almost anything is possible. If you want to return the position, are you wanting the row number or the cell reference? Part of the formula I gave you will return the row number where the number lies, but all of the row numbers are in an array in the formula and I don't know if you want an array of positions or a single position.

 

[graham84]

Hey, Well we decided that its probably best to remove the gaps, we're dealing with too much data, and it'll be faster and cleaner.

However I've never looked at array formulas before, so I'm still messing about with what you posted to see how they work. Early I replaced the {1,2,3.........} part with an offset of numbered rows, and I was able to increase or decrease the parameter of the range. That seemed to work well. And I replaced the sum with average, and that worked good.

So just out of curiosity, I wanted to know if getting the single start position was possible. It would be useful to know, and learn from.

Thanks alot.

 

[Trouttrap2]

If you removed all of the '--' or blanks because of too much data to work with, then you just need the last 10 rows, right? If that is the case, then a simple formula that sums or if you want average the last 10 rows would be entered into B1: =SUM(A1:A10) and drag down. This will sum 10 rows, and the range will will move as you drag down. You don't need the OFFSET function. Sorry, I don't know what you mean by the single start position.