number of occurences of a value greater than another value within a repeating range

C

ch5497

New Member
Joined
Jan 28, 2015
Messages
4
Hello all,

The Problem.
I have some data formatted into three columns, the first column is the date, the second column is the time (in 30 minute increments) and the third is some value assigned with that specific time (see example below). The amount of days will vary and loop midnight to midnight, and unfortunately the format the dates and times come in isn't great.

What I require seems simple but I can't figure out a way of doing it. I need to be able to determine whether the third column (MVA_FIGURE) is greater than a variable number for a variable number of instances or more for any given 24 hour period. So, looking again at the below example, if my variable number was 280 and I wanted to know if it occured for more than six hours within this data set, by inspection I could see that it's greater than my variable number (280) six times (1700 - 2000) which equates to three hours (as each row is half an hour). So in this case, it wouldn't flag up as hitting the criteria.

Final point, the occurences do not neccessarily have to occur sequentially, it's purely number of occurences within any 24 hour period.

I realise this is a fairly obscure request, and I apologise if I haven't been clear on any points. I would, however, appreciate any help I could get.



Example.
[TABLE="width: 201"]
<TBODY>[TR]
[TD]GDATE</SPAN>
[/TD]
[TD]GTIME</SPAN>
[/TD]
[TD]MVA_FIGURE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD="align: right"]204.9895063</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]100</SPAN>
[/TD]
[TD="align: right"]199.5723453</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]130</SPAN>
[/TD]
[TD="align: right"]194.7255063</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]200</SPAN>
[/TD]
[TD="align: right"]186.7477925</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]230</SPAN>
[/TD]
[TD="align: right"]181.6066326</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]300</SPAN>
[/TD]
[TD="align: right"]184.9861843</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]330</SPAN>
[/TD]
[TD="align: right"]180.2094126</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]400</SPAN>
[/TD]
[TD="align: right"]174.7053937</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]430</SPAN>
[/TD]
[TD="align: right"]172.5394083</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]500</SPAN>
[/TD]
[TD="align: right"]167.4254404</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]530</SPAN>
[/TD]
[TD="align: right"]167.1947015</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]600</SPAN>
[/TD]
[TD="align: right"]167.6031566</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]630</SPAN>
[/TD]
[TD="align: right"]170.1826645</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]700</SPAN>
[/TD]
[TD="align: right"]172.928321</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]730</SPAN>
[/TD]
[TD="align: right"]176.2564385</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]800</SPAN>
[/TD]
[TD="align: right"]176.0711961</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]830</SPAN>
[/TD]
[TD="align: right"]179.260499</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]900</SPAN>
[/TD]
[TD="align: right"]185.1718251</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]930</SPAN>
[/TD]
[TD="align: right"]192.7740556</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1000</SPAN>
[/TD]
[TD="align: right"]202.4699188</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1030</SPAN>
[/TD]
[TD="align: right"]213.2944512</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1100</SPAN>
[/TD]
[TD="align: right"]222.5127387</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1130</SPAN>
[/TD]
[TD="align: right"]231.8210767</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1200</SPAN>
[/TD]
[TD="align: right"]240.3002555</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1230</SPAN>
[/TD]
[TD="align: right"]250.3585271</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1300</SPAN>
[/TD]
[TD="align: right"]254.4970218</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1330</SPAN>
[/TD]
[TD="align: right"]257.2482795</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1400</SPAN>
[/TD]
[TD="align: right"]257.7934439</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1430</SPAN>
[/TD]
[TD="align: right"]260.8439491</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1500</SPAN>
[/TD]
[TD="align: right"]260.5250671</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1530</SPAN>
[/TD]
[TD="align: right"]259.9072946</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1600</SPAN>
[/TD]
[TD="align: right"]265.6289623</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1630</SPAN>
[/TD]
[TD="align: right"]277.1440293</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1700</SPAN>
[/TD]
[TD="align: right"]285.4877687</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1730</SPAN>
[/TD]
[TD="align: right"]293.247261</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1800</SPAN>
[/TD]
[TD="align: right"]296.197911</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1830</SPAN>
[/TD]
[TD="align: right"]293.9217653</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1900</SPAN>
[/TD]
[TD="align: right"]288.9328161</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]1930</SPAN>
[/TD]
[TD="align: right"]280.6324322</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2000</SPAN>
[/TD]
[TD="align: right"]271.5594419</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2030</SPAN>
[/TD]
[TD="align: right"]262.1661193</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2100</SPAN>
[/TD]
[TD="align: right"]251.7013216</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2130</SPAN>
[/TD]
[TD="align: right"]242.9805696</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2200</SPAN>
[/TD]
[TD="align: right"]231.8743509</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2230</SPAN>
[/TD]
[TD="align: right"]224.1486197</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2300</SPAN>
[/TD]
[TD="align: right"]221.1319337</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2330</SPAN>
[/TD]
[TD="align: right"]206.2547987</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140101</SPAN>
[/TD]
[TD="align: right"]2400</SPAN>
[/TD]
[TD="align: right"]195.8922012</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140102</SPAN>
[/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD="align: right"]185.9825833</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140102</SPAN>
[/TD]
[TD="align: right"]100</SPAN>
[/TD]
[TD="align: right"]178.5772018</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140102</SPAN>
[/TD]
[TD="align: right"]130</SPAN>
[/TD]
[TD="align: right"]173.3794973</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140102</SPAN>
[/TD]
[TD="align: right"]200</SPAN>
[/TD]
[TD="align: right"]169.0231872</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20140102</SPAN>
[/TD]
[TD="align: right"]230</SPAN>
[/TD]
[TD="align: right"]164.06748</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>
[/TD]
[TD="align: right"]




</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
575
madi1004
M
B
  • Solved
Formulas 1) to return the date of the first occurrence of the highest value; and 2) to count the number of occurrences
Replies
5
Views
498
Eric W
Eric W
A
  • Question Question
VBA Solution To Find Position In A Series of Cells
Replies
2
Views
454
Ark68
A
J
  • Question Question
Formula for the number of times selected dates occur within a range spanning multiple years
Replies
0
Views
280
JulianRennie
J
M
  • Question Question
Excel crashing - multiple interacting macros
Replies
2
Views
2K
mhwolog
M

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top