Identifying consecutive data that average to a positive number

[pbgexcel]

Good morning all,

I have in an Excel sheet a long vertical list of ~random positive and negative numbers.

Starting from the first number at the top, I'm looking to average 2 consecutive numbers downwards, and flag them if they average to a positive number.

I also want to check if either or both of the numbers immediately above and below these two numbers can be included and their total average remain positive.

If either number above or below can be included, I then want to check the next adjacent number(s), and again see if it/they can be included and the total average remain positive. I want to repeat this process down a column containing many thousands of numbers.

Any ideas as to how to go about this with formulas? I'm very familiar with VBA, but hope to avoid having to write an iterative code to do this which will be tedious and almost certainly slow. Any and all help greatly appreciated! Please let me know if I can clarify in any way.

Thanks so much!

 

[Eric W]

Happy New Year, and welcome to the forum.

I think I'd disagree with you. I can imagine formulas to do what you want, but they'd be complicated array formulas, and thousands of copies of them would certainly slow down your sheet. A macro would only be run on demand, and if written properly, should be pretty quick.

But I think your question needs a bit more definition. Consider this small range:

[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]-1[/TD]
[/TR]
[TR]
[TD]-6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]-5[/TD]
[/TR]
</tbody>[/TABLE]

The first 2 cells average -3.5, so continue. Then -6 and 10 average 2, so flag it. Now check the row below it, and -6,10,-5 average -1/3 so don't flag. But -1,-6,10 average 1, so flag it. And all 4 average -1/2, so don't. I'm a bit confused as to which ones to use, and how to continue.

My best guess as to what you'd like is: Step 1: find 2 cells that average to a positive. Step 2: include the cell above the range, and see if it's still positive. If so, set that to the new range, and loop back to Step 2. Step 3: If not, include the cell below the range, and see if it's still positive. If so, set that to the new range, and loop back to Step 2. Step 4: Flag this range as being done. Step 5: Go back to Step 1, starting at the row right below the last row found in Step 4. At the end of the entire range, quit.

And how do you want it flagged? In the next cell to the right, include a message with the average, plus the top and bottom rows in the range?

 

[pbgexcel]

Eric, Same to you, thank you very much!

I suppose you may be right..I'm currently writing the logic for the macro. Any ideas how to store the average and also starting and ending cells for each range in an array that can be printed out to a new sheet at the end of the macro?

Thanks for your help!

 

[Eric W]

The biggest thing to remember if you have a lot of data and want the macro to run quickly, is to minimize reading/writing to the worksheets. That's about the slowest thing you can do in VBA. It's usually best to read all the data you need at the start into internal arrays, process it from the arrays, then write out everything at the end. For example:

Sub test1()
Dim MyData As Variant, MyResults()
Dim lr As Long, NumResults As Long


    NumResults = 0                                              ' Set the number of results to 0
    lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row  ' Find the last row
    MyData = Sheets("Sheet2").Range("A1:A" & lr).Value          ' Read the entire range in one shot
                                                                ' Now do ALL your processing from MyData, NOT the sheet
    
    ReDim MyResults(1 To lr, 1 To 3)                            ' Set the results table to the max size
    
' Do stuff here


' Let's say we found one:
    NumResults = NumResults + 1
    MyResults(NumResults, 1) = MyAverage
    MyResults(NumResults, 2) = MyTopRow
    MyResults(NumResults, 3) = MyBottomRow
    
' Loop back and find some more.  When all done:


    Sheets("Sheet3").Range("A1:C" & lr).Value = MyResults       ' Write out the results in one shot
                                                                ' Includes possibly many blank rows
    
End Sub

It's possible to avoid printing blank rows at the end, but in this case it's probably not worth the effort, since those rows must be blank anyway since you don't know how many results you'll have.

Edit: FYI, MyData will be a 2-D array, even if you're only reading 1 column. Reference the contents with MyData(MyRow, 1).

 

[pbgexcel]

That approach is a huge help; would be valuable to implement in other of my macros too and certainly in the future.

Thanks so much for all your help, Eric!

 

[Eric W]

Happy to help!