Counting number of times a maximum exceeds a threshold on a certain frequency

varsakov

New Member
Joined
May 4, 2013
Messages
12
Hi All, this is the first time I'm on this forum - I apologize in advance if my question isn't as detailed as it should be. Anyway, I was hoping someone could point me in the right direction for a macro I am trying to create.

I have a set of data that is as following: in Column A I have a list of months denoted by (10,11,12 for October, November, December respectively). 10 appears multiple times in the column on different rows to account for different days of october, the same applies to 11 (November) and 12 (December). In columns B through E I have different times of the days. So for instance, one column will have data for 1:00am on October 2nd, and another column will have data for 2:00am on October second. These day were randomly generated from 0 to 120.


Ultimately, I am hopping to create a macro that will spit out the sum of times in the three months mentioned where the maximum monthly figure of each month exceeded a threshold of say 100.

I am thinking it might need a loop of some sort to count the maximum of each month if it exceeds the threshold. Does anyone have any ideas on how to do this? I'm a beginner VBA coder and am trying to see some examples to become more advanced. I really appreciate the help!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
Can be also done with formulas.

Why not post a small sample with data and the expected result?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,990
I do not understand what you want. What is the data in B2 to E10 What is the threshold. Do you want to count every time the threshold is exceeded and report that number for each month. Or do you want to see how many times the threshold was exceeded for EACH time?
 

varsakov

New Member
Joined
May 4, 2013
Messages
12
Month1:00am2:00am3:00am4:00amMaximum on Each DayMaximum for the MonthNumber of Times in the last three month the maximum monthly figures exceeded 100
1029141011011122This is the number I am looking to get with the Macro
1065911018101
10110927899110
10112164990112
111453759090111
118051127080
11931154193
11857128185
1122111826111
113656478787
1245750777799
123786862886
12387402340
129980205699
122210836583

<colgroup><col span="8"><col><col span="4"></colgroup><tbody>
</tbody>
 

varsakov

New Member
Joined
May 4, 2013
Messages
12
Can be also done with formulas.

Why not post a small sample with data and the expected result?
Hi Aladin, Thank you for offering to take a look. I couldn't figure out a way to attach, so I have copy and pasted the table below:
Month1:00am2:00am3:00am4:00amMaximum on Each DayMaximum for the MonthNumber of Times in the last three month the maximum monthly figures exceeded 100
1029141011011122This is the number I am looking to get with the Macro
1065911018101
10110927899110
10112164990112
111453759090111
118051127080
11931154193
11857128185
1122111826111
113656478787
1245750777799
123786862886
12387402340
129980205699
122210836583

<colgroup><col span="8"><col><col span="4"></colgroup><tbody>
</tbody>
Let me know if I can further clarify! Thank you.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
Thanks for the exhibit and specs.

Max/If
100
10
29
1
4
101
101
112
10
10
65
91
101
8
101
112
11
10
110
92
78
99
110
112
12
10
112
16
49
90
112
112
11
14
53
75
90
90
111
2
11
80
51
12
70
80
111
11
93
11
5
41
93
111
11
85
71
28
1
85
111
11
22
111
8
26
111
111
11
36
56
47
87
87
111
12
4
57
50
77
77
99
12
37
86
86
28
86
99
12
38
7
40
23
40
99
12
99
80
20
56
99
99
12
22
10
83
65
83
99

<tbody>
</tbody>

I1: 100 (the criterion value)

I2:I4, months (mont numbers) of interest.

G2, control+shift+enter, not just enter:
Rich (BB code):
=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))
I6, control+shift+enter:
Rich (BB code):
=SUM((IF(FREQUENCY(IF(ISNUMBER(MATCH($A$2:$A$16,$I$2:$I$4,0)),
  MATCH($G$2:$G$16,$G$2:$G$16,0)),ROW($G$2:$G$16)-ROW($G$2)+1),
   $G$2:$G$16,-9.99999999999999E+307)>I1)+0)
 

varsakov

New Member
Joined
May 4, 2013
Messages
12
Thanks for the exhibit and specs.

Max/If100
10291410110111210
106591101810111211
1011092789911011212
10112164990112112
1114537590901112
118051127080111
11931154193111
11857128185111
1122111826111111
113656478787111
1245750777799
12378686288699
1238740234099
12998020569999
12221083658399

<tbody>
</tbody>

I1: 100 (the criterion value)

I2:I4, months (mont numbers) of interest.

G2, control+shift+enter, not just enter:
Rich (BB code):
=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))
I6, control+shift+enter:
Rich (BB code):
=SUM((IF(FREQUENCY(IF(ISNUMBER(MATCH($A$2:$A$16,$I$2:$I$4,0)),
  MATCH($G$2:$G$16,$G$2:$G$16,0)),ROW($G$2:$G$16)-ROW($G$2)+1),
   $G$2:$G$16,-9.99999999999999E+307)>I1)+0)
Thank you Aladin! I see your point of view.. I tried something similar to what you had above using Max, Offset, Match and Countif and returned correct numbers. No macro necessary I guess. Thanks for the help!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
Thank you Aladin! I see your point of view.. I tried something similar to what you had above using Max, Offset, Match and Countif and returned correct numbers. No macro necessary I guess. Thanks for the help!!
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,934
Messages
5,411,346
Members
403,362
Latest member
DoubleJay

This Week's Hot Topics

Top