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

[varsakov]

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!

 

[Aladin Akyurek]

Can be also done with formulas.

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

 

[oldbrewer]

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]

Month	1:00am	2:00am	3:00am	4:00am	Maximum on Each Day	Maximum for the Month		Number of Times in the last three month the maximum monthly figures exceeded 100
10	29	1	4	101	101	112		2	This is the number I am looking to get with the Macro
10	65	91	101	8	101
10	110	92	78	99	110
10	112	16	49	90	112
11	14	53	75	90	90	111
11	80	51	12	70	80
11	93	11	5	41	93
11	85	71	28	1	85
11	22	111	8	26	111
11	36	56	47	87	87
12	4	57	50	77	77	99
12	37	86	86	28	86
12	38	7	40	23	40
12	99	80	20	56	99
12	22	10	83	65	83

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

 

[varsakov]

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:

Month	1:00am	2:00am	3:00am	4:00am	Maximum on Each Day	Maximum for the Month		Number of Times in the last three month the maximum monthly figures exceeded 100
10	29	1	4	101	101	112		2	This is the number I am looking to get with the Macro
10	65	91	101	8	101
10	110	92	78	99	110
10	112	16	49	90	112
11	14	53	75	90	90	111
11	80	51	12	70	80
11	93	11	5	41	93
11	85	71	28	1	85
11	22	111	8	26	111
11	36	56	47	87	87
12	4	57	50	77	77	99
12	37	86	86	28	86
12	38	7	40	23	40
12	99	80	20	56	99
12	22	10	83	65	83

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

Let me know if I can further clarify! Thank you.

 

[Aladin Akyurek]

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:

=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))

I6, control+shift+enter:

=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]

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:

=MAX(IF($A$2:$A$16=A2,$B$2:$F$16))

I6, control+shift+enter:

=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]

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.