Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
Hiya,
I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell. For example, There are 2 numbers involved, 0 and 1. My spreadsheet looks like: A1 A2 1 A3 0 A4 0 A5 0 A6 0 A7 1 A8 0 A9 0 and so on..... Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared. In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1. I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
With possible values in A2:A30
This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of just ENTER) returns the maximum consecutive occurrences of zero. C1: =MAX(FREQUENCY(IF((A2:A30<"")*(A2:A30=0),ROW(A2:A 30)), IF(A2:A30<0,ROW(A2:A30)))) Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Samuel" wrote in message ... Hiya, I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell. For example, There are 2 numbers involved, 0 and 1. My spreadsheet looks like: A1 A2 1 A3 0 A4 0 A5 0 A6 0 A7 1 A8 0 A9 0 and so on..... Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared. In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1. I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
Samual,
I could manage to count the zeros with a formula but can't do the reset so here's a VB solution. Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Or _ Target.Column < 1 Then Exit Sub Dim ScoreRange As Range Set ScoreRange = Range("C1") lastrow = Cells(Rows.Count, "A").End(xlUp).Row For X = lastrow To 2 Step -1 If IsNumeric(Cells(X, 1)) And Cells(X, 1).Value = 0 Then zeroes = zeroes + 1 Else Application.EnableEvents = False ScoreRange = zeroes Application.EnableEvents = True Exit For End If Next End Sub Mike "Samuel" wrote: Hiya, I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell. For example, There are 2 numbers involved, 0 and 1. My spreadsheet looks like: A1 A2 1 A3 0 A4 0 A5 0 A6 0 A7 1 A8 0 A9 0 and so on..... Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared. In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1. I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
Slight addition,
Put this line at the start of the code zeroes = 0 Mike "Mike H" wrote: Samual, I could manage to count the zeros with a formula but can't do the reset so here's a VB solution. Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Or _ Target.Column < 1 Then Exit Sub Dim ScoreRange As Range Set ScoreRange = Range("C1") lastrow = Cells(Rows.Count, "A").End(xlUp).Row For X = lastrow To 2 Step -1 If IsNumeric(Cells(X, 1)) And Cells(X, 1).Value = 0 Then zeroes = zeroes + 1 Else Application.EnableEvents = False ScoreRange = zeroes Application.EnableEvents = True Exit For End If Next End Sub Mike "Samuel" wrote: Hiya, I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell. For example, There are 2 numbers involved, 0 and 1. My spreadsheet looks like: A1 A2 1 A3 0 A4 0 A5 0 A6 0 A7 1 A8 0 A9 0 and so on..... Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared. In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1. I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
Hello,
If I understand the question correctly, enter normally into C1 =MAX(0,LOOKUP(2,1/("0"=""&A2:A30),ROW(A2:A30))-LOOKUP(2,1/ (1=A2:A30),ROW(A2:A30))) Regards, Bernd |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
I think I misunderstood what you're looking for.
The formula I posted calculates the maximum consecutive occurrences of zero. Not the count of consecutive zeros AFTER the last 1. Bernd P seems to have gotten it right. Regards, Ron Coderre Microsoft MVP (Excel) "Ron Coderre" wrote in message ... With possible values in A2:A30 This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of just ENTER) returns the maximum consecutive occurrences of zero. C1: =MAX(FREQUENCY(IF((A2:A30<"")*(A2:A30=0),ROW(A2:A 30)), IF(A2:A30<0,ROW(A2:A30)))) Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Samuel" wrote in message ... Hiya, I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell. For example, There are 2 numbers involved, 0 and 1. My spreadsheet looks like: A1 A2 1 A3 0 A4 0 A5 0 A6 0 A7 1 A8 0 A9 0 and so on..... Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared. In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1. I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating largest gap in a series of consecutive numbers
Thank you all very much! This has really helped me a lot! :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating the Average for non consecutive cells using custom for | Excel Discussion (Misc queries) | |||
Counting the largest number of consecutive 1's in a column | New Users to Excel | |||
formula for returning the largest letter in a series of letters | Excel Worksheet Functions | |||
3 largest numbers | Excel Discussion (Misc queries) | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions |