Extracting result from data

graham84

New Member
Joined
Nov 29, 2010
Messages
31
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

graham84

New Member
Joined
Nov 29, 2010
Messages
31
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

Well-known Member
Joined
May 11, 2010
Messages
612
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

New Member
Joined
Nov 29, 2010
Messages
31
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. :(


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

New Member
Joined
Nov 29, 2010
Messages
31

ADVERTISEMENT

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


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

New Member
Joined
Nov 29, 2010
Messages
31
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 :biggrin:

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

New Member
Joined
Nov 29, 2010
Messages
31

ADVERTISEMENT

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

Well-known Member
Joined
May 11, 2010
Messages
612
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

New Member
Joined
Nov 29, 2010
Messages
31
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. :)
 
Last edited:

Trouttrap2

Well-known Member
Joined
May 11, 2010
Messages
612
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,464
Messages
5,601,817
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top