Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count maximum consecutive positive numbers
In Excel, my challenge is to count the maximum number of consecutive
positive numbers in a column consisting of blank cells and formulas that evaluate to numbers and "". For example, starting in A1, the cells evaluate to: 1 -2 3 5 -6 The result would be 2 (counting the two consecutive positive numbers, 3 and 5, above and below the blank or "" cell). This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. They say it can't be done and I need to confirm that. Thank you all, JP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count maximum consecutive positive numbers
The below assumes that data is in column 1 and starts row
2 or lower. The answer will be in column 2 1 row above. The macro replaces the ""'s with 0's to do the calcs and rhe re-replaces the 0's with ""'s. John Sub Macro1() ' ' Macro1 Macro ' Sheets("sheet1").Activate Dim rng As Range Range("b1").EntireColumn.ClearContents Cells(1, 1).End(xlDown).CurrentRegion.Name = "data" Range("data").Select Selection.Replace What:="""""", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row start: Set rng = Sheets("sheet1").Range("data").Find(" ") If rng Is Nothing Then GoTo continue rngrow = ActiveSheet.Range("rng").Row Cells(rngrow, 1).EntireRow.Delete If rngrow bottomrow Then GoTo continue GoTo start continue: toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row j = 0 For i = toprow To bottomrow Cells(i, 1).Select If Cells(i, 1).Value 0 Then j = j + 1 If Cells(i, 1).Value = 0 Then j = j If Cells(i, 1).Value < 0 Then j = 0 Cells(i, 2).Value = j GoTo nexti nexti: Next i Range("data").Select Selection.Replace What:="0", Replacement:="""""", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Max = 0 For i = toprow To bottomrow If Cells(i, 2).Value Max Then Max = Cells(i, 2).Value Next i Cells(toprow - 1, 2).Value = Max End Sub -----Original Message----- In Excel, my challenge is to count the maximum number of consecutive positive numbers in a column consisting of blank cells and formulas that evaluate to numbers and "". For example, starting in A1, the cells evaluate to: 1 -2 3 5 -6 The result would be 2 (counting the two consecutive positive numbers, 3 and 5, above and below the blank or "" cell). This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. They say it can't be done and I need to confirm that. Thank you all, JP . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count maximum consecutive positive numbers
Just saw I left a bunch of code in that isn't needed! I
had been search for the ""'s but gave up on that idea and just replaced them. Section to be removed is noted below. John -----Original Message----- The below assumes that data is in column 1 and starts row 2 or lower. The answer will be in column 2 1 row above. The macro replaces the ""'s with 0's to do the calcs and rhe re-replaces the 0's with ""'s. John Sub Macro1() ' ' Macro1 Macro ' Sheets("sheet1").Activate Dim rng As Range Range("b1").EntireColumn.ClearContents Cells(1, 1).End(xlDown).CurrentRegion.Name = "data" Range("data").Select Selection.Replace What:="""""", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row remove below start: Set rng = Sheets("sheet1").Range("data").Find(" ") If rng Is Nothing Then GoTo continue rngrow = ActiveSheet.Range("rng").Row Cells(rngrow, 1).EntireRow.Delete If rngrow bottomrow Then GoTo continue GoTo start continue: remove above toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row j = 0 For i = toprow To bottomrow Cells(i, 1).Select If Cells(i, 1).Value 0 Then j = j + 1 If Cells(i, 1).Value = 0 Then j = j If Cells(i, 1).Value < 0 Then j = 0 Cells(i, 2).Value = j GoTo nexti nexti: Next i Range("data").Select Selection.Replace What:="0", Replacement:="""""", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Max = 0 For i = toprow To bottomrow If Cells(i, 2).Value Max Then Max = Cells(i, 2).Value Next i Cells(toprow - 1, 2).Value = Max End Sub -----Original Message----- In Excel, my challenge is to count the maximum number of consecutive positive numbers in a column consisting of blank cells and formulas that evaluate to numbers and "". For example, starting in A1, the cells evaluate to: 1 -2 3 5 -6 The result would be 2 (counting the two consecutive positive numbers, 3 and 5, above and below the blank or "" cell). This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. They say it can't be done and I need to confirm that. Thank you all, JP . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count maximum consecutive positive numbers
the OP said:
This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. Of course I have seen some OP's get all enthusiastic when they received something they specifically said they didn't want - however, this person said they were evaluating a "this can't be done in a single formula" challenge. Not sure why he posted in programming instead of the more logical Worksheet.Functions -- Regards, Tom Ogilvy wrote in message ... Just saw I left a bunch of code in that isn't needed! I had been search for the ""'s but gave up on that idea and just replaced them. Section to be removed is noted below. John -----Original Message----- The below assumes that data is in column 1 and starts row 2 or lower. The answer will be in column 2 1 row above. The macro replaces the ""'s with 0's to do the calcs and rhe re-replaces the 0's with ""'s. John Sub Macro1() ' ' Macro1 Macro ' Sheets("sheet1").Activate Dim rng As Range Range("b1").EntireColumn.ClearContents Cells(1, 1).End(xlDown).CurrentRegion.Name = "data" Range("data").Select Selection.Replace What:="""""", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row remove below start: Set rng = Sheets("sheet1").Range("data").Find(" ") If rng Is Nothing Then GoTo continue rngrow = ActiveSheet.Range("rng").Row Cells(rngrow, 1).EntireRow.Delete If rngrow bottomrow Then GoTo continue GoTo start continue: remove above toprow = Range("A1").End(xlDown).Row bottomrow = ActiveSheet.Cells(Rows.Count, _ "A").End(xlUp).Row j = 0 For i = toprow To bottomrow Cells(i, 1).Select If Cells(i, 1).Value 0 Then j = j + 1 If Cells(i, 1).Value = 0 Then j = j If Cells(i, 1).Value < 0 Then j = 0 Cells(i, 2).Value = j GoTo nexti nexti: Next i Range("data").Select Selection.Replace What:="0", Replacement:="""""", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Max = 0 For i = toprow To bottomrow If Cells(i, 2).Value Max Then Max = Cells(i, 2).Value Next i Cells(toprow - 1, 2).Value = Max End Sub -----Original Message----- In Excel, my challenge is to count the maximum number of consecutive positive numbers in a column consisting of blank cells and formulas that evaluate to numbers and "". For example, starting in A1, the cells evaluate to: 1 -2 3 5 -6 The result would be 2 (counting the two consecutive positive numbers, 3 and 5, above and below the blank or "" cell). This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. They say it can't be done and I need to confirm that. Thank you all, JP . . |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count maximum consecutive positive numbers
"Tom Ogilvy" wrote...
the OP said: This needs to be done using a single formula or array formula, without using a separate running total column using =MAX, and without using a UDF. .... . . . however, this person said they were evaluating a "this can't be done in a single formula" challenge. Not sure why he posted in programming instead of the more logical Worksheet.Functions Indeed, so I'm crossposting to worksheet.functions. Follow-up there. Of course it can be done in a single array formula - just barely. If the data were in a single column by multiple row range named rec, then try the following. =COUNT(OFFSET(rec,MATCH(MAX( MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))) -(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))), --((rec<0)*ROW(rec)=TRANSPOSE(ROW(rec))))0),1-(rec<0))), MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))) -(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))), --((rec<0)*ROW(rec)=TRANSPOSE(ROW(rec))))0),1-(rec<0)),0) -MAX(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))) -(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))), --((rec<0)*ROW(rec)=TRANSPOSE(ROW(rec))))0),1-(rec<0))),0, MAX(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))) -(MMULT(--(ROW(rec)=TRANSPOSE(ROW(rec))), --((rec<0)*ROW(rec)=TRANSPOSE(ROW(rec))))0),1-(rec<0))),1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding maximum, minimum in a range consists both Positive and Negative numbers | Excel Worksheet Functions | |||
count current run of positive numbers | New Users to Excel | |||
Count cells with positive numbers | Excel Discussion (Misc queries) | |||
how to add consecutive positive numbers | Excel Discussion (Misc queries) | |||
Count Consecutive Numbers in a Row | Excel Worksheet Functions |