I have a column of numbers in col C, the numbers vary from -2.00 to +2.00. I’d like a macro that will help find all the group of next-to-each-other negative numbers and compare them and let me know which one is the longest.
EX:
-0.25
-0.72
-0.43
0.05
0.62
-0.05
-0.29
-0.06
-0.34
0.46
1.52
0.96
-0.88
0.55
The answer is 4 because it has four numbers next to each other less than zero.
VBisgreat,
One way...
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
How about just a formula?
=MAX(FREQUENCY(IF(A1:A14<0, ROW(A1:A14)), IF(A1:A14>=0, ROW(A1:A14))))
The formula MUST be confirmed with Ctrl+Shift+Enter
Entia non sunt multiplicanda sine necessitate
Is there a way to tell where longest occurred on the list?
Similarly, I want to know which group happen most often. In this case I want the answer to be 3 because the 3-next-to-each-other negative group appeared most often (twice). Thank you so much!!!
Ex:
-0.25
-0.72
-0.43
0.05
0.62
-0.05
-0.29
-0.06
0.46
1.52
0.96
-0.88
-452
0.55
1.00
1.87
-0.05
-0.29
-0.06
-0.66
1.34
The fomular doesn't work because the data is in col C. Thanks though!
Is there a way to tell where the on the list that group occurred? Can you help modify it so that this time instead of finding the longest it will find the one appear most frequent?
ex:
-0.25
-0.72
-0.43
0.05
0.62
-0.05
-0.29
-0.06
0.46
1.52
0.96
-0.88
0.55
the answer is 3 because the three group appearred twice.
Thank you,
VB
That's quite an obstacle to overcome--good luck.Originally Posted by VBisgreat
VBisgreat,
Perhaps...
Please Login or Register to view this content.
Thank you all.
Can you add this computation in there too?
Ex:
0.62
-0.05
-0.29
-0.06
0.46
1.52
0.96
-0.34
-0.88
0.29
-0.25
-0.72
-0.43
0.05
The most common group is 3 and the average of the positive numbers one place above them is (0.62+0.29)/2=0.455
Do the same thing for maximum group. Find the average of the positive numbers one place above it.
VBisgreat,
Updated code:
Please Login or Register to view this content.
thank you very much!
I tried running the data using the previous code and this code, the previous one came out for the common group 5 appeared 10 times, but this code shown common group as 4 appeared 8 time. The max groupd is good.
Last edited by VBisgreat; 03-26-2013 at 02:52 PM.
VBisgreat,
I found the bug and here is the updated code. With that dataset, the most common streak is actually 1 with 88 occurrences. Would you rather that streaks of 1 be ignored? The second most common is streaks of 2 with 42 occurrences. This code will include streaks of 1.
Please Login or Register to view this content.
ignore 1 streak please. Thanks.
This code will ignore streaks of 1:
Please Login or Register to view this content.
Can have them list from max group, most common, second common...ect? just wondering
or just list them out max streak, second, third, ect... and I can look at it and determine the most common streak...
Last edited by VBisgreat; 03-21-2013 at 04:28 PM.
VBisgreat,
This code will create a new worksheet and output the results to this new sheet. It will sort the results by number of appearances so that the most common is on top, 2nd-most common is next and so on. It will then highlight the maximum group size wherever it is in the list so that it can be easily referenced.
Please Login or Register to view this content.
Can you put it in a pop up window, I really like the pop-up window. Very convenience. Thanks.
The information won't all fit in a msgbox (popup window).
error at: dAvg(i) = dAvg(i) + rngArea.Offset(-1).Cells(1).Value
VBisgreat,
I am able to run the code with no issues and it performs as expected. Attached is an example workbook that contains the macro. When I run it, I get the results sheet successfully.
Ah, I was able to replicate the error. Update that line of code to the following:
Please Login or Register to view this content.
Also, make sure you have a header cell so that actual data starts below row 1.
since you have it in another worksheet, can you include the 1 streak too? many thanks.
Just change this line:
Please Login or Register to view this content.
So that it is > 0 instead of > 1
That will include the 1 streaks.
oh wait, there are some other modifications too, just a sec
Updated code:
Please Login or Register to view this content.
It's still ignoring the 1 streak.
That's what I get for not testing before posting /sigh
This one works
Please Login or Register to view this content.
I ran the two code and compared them but the answer from with and without 1 streak don't match up.
size appear
1 88
2 42
3 15
4 9
5 9
6 4
7 2
8 1
Size Appearances
2 88
3 42
4 15
5 9
6 9
7 4
8 2
The first one is correct, we already know this because I stated earlier that there are 88 1-streaks and 42 2-streaks
The second size/appearances you listed are from the incorrect code that was "ignoring" 1-streaks. It wasn't ignoring them, but it wasn't outputting the display properly which is what I fixed in the most recent code.
Thank you very much!!!
VBisgreat!!
keep getting error here sometimes
dAvg(i) = dAvg(i) + rngArea.Offset(-1).Cells(1).Value
nevermind. Thanks!!
tigeravatar,
Is there a way you can help me put the output (group size, appearances, ect..) on the same sheet instead of adding another sheet then put it there? it will help make it easier for me. Please put it starting on cell J25.
Thanks sincerely,
VBisgreat
Updated code:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks