Need help with max formula!

Max.png



I am currently running a timed experiment where I have a column of increasing times (column A) with a set of values (column C) recorded at those times. The software I am using generates 3-5 data points per second and I have ~50,000 rows I really (REALLY) want to sift through with a formula instead of manually. I would have to scroll through 100 pages to manually select a range

Basically, I want to define a range of times using a upper/lower limit (column A), and obtain the max value of column C within that time range.

________
e.g. at 14:04:59, a drug dose is given to measure a response that has a peak before the next dose is given at 14:15:11. I want to find that peak (i.e. max) value.

So, between the times 14:04:59 and 14:15:11 (represented in column A), I want to find to find the max in column C within that reference range. 14:04:59 would define the lower limit and 14:15:11 would define the upper limit of that range in column A.


Thanks in advance! First time poster here.

not sure how you are defining range of times
sounds like you need to use max formula or some kind of combination of MAX/IF or even aggregate formula

Attach a small sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

this is the formula i came up with
it is an array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula:

Please Login or Register  to view this content.

reason i added the MOD in there is because the time in column A has the date affixed to it as well
so MOD(,1) just strips the date off and leaves the time

D7 is lower and E7 is upper...i had the headings on backwards but you get the idea
greater than and equal to D7 less than and equal to E7

sorry slight change in formula
still CSE

Formula:

Please Login or Register  to view this content.

hmm this formula works perfectly when dealing with dates...it seems to be time that is messing it up
ill ask others to see if they can chime in

actually just change the upper and lower
copy and paste the number you want to start and end with
then change formula to not have MOD at all

Formula:

Please Login or Register  to view this content.