Find The minimum value in a range within a range.

[AJPlant]

Hi,

Not sure if what I want to do is even possible in excel, but let me give an example of what I am trying to automate.

I have two columns of data that is exported to me (Very large). I have shortened the one in the example to easily explain what I want to do.
Column A tracks the elapsed time in milliseconds
Column B tracks the Load measurement.

I want to automatically find the highest "Minimum" load value that is held for 3 seconds, within the range of loads above a given target load.

So in my example, you can see for a target load of 80N, the highest minimum load value that is held for 3 seconds is 100. However, there are numerous ranges that are held for 3 seconds as seen by the other boxes I have made to illustrate this point.

Is it possible to automate this process to tell me this information? Currently I am looking through the datasets manually - which are much longer and are not as easy to instantly distinguish the highest minimum load at first glance as the example I provided. Thanks very much in advance!

 

[etaf]

or is this just highlighting anything that is higher than target load cell which has 80 and higher than 3000 ms

what in your example is the

highest minimum load at first glance as the example I provided.

81 , as that is the nearest to 80 and the minimum to 80
OR 84 ???
Not sure why its 100

the highest minimum load value that is held for 3 seconds is 100

as thats 6seconds
OR is that difference between the 3 seconds and where it reaches max load and then hold for an additional 3 seconds

so if 23 seconds it reached 80 , then held for 3 seconds would be 26 seconds - whats the load then - but it could be lower

sorry , i'm probably missing something ?
not sure what the BOXED text is for or what you want there

in conditional formatting you could use
=AND ( $A2 >= 3000 , $B2 >= $C$2 )

i'll add to spreadsheet and post sample here using XL2BB

Book2
	A	B	C
1	Elapsed mS	Load Cell	Target Load
2	1000	0	80
3	2000	20
4	3000	66
5	4000	84
6	5000	95
7	6000	100
8	7000	105
9	8000	107
10	9000	93
11	10000	81
12	11000	74
13	12000	60
14	13000	33
15	14000	3
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B17	Expression	=AND($A2>=3000,$B2>=$C$2)	text	NO

Will it always be a curve distribution , so goes up and then comes down , never oscillates in the middle - bell curve type of thing
Tensile strength testing i guess, from my college days 40+ years ago

 

[AJPlant]

Hi,

Thanks for your response!

I am probably doing a poor job of explaining, but when I say "Highest Minimum Load Above Target load for 3 seconds", I mean:

- For any 3 second period within the range above the target load (green cells), what 3 second period gives you the highest minimum load. So the boxes I provided as examples are some of the "3 second periods above target load of 80". As you can see, the centre box has loads of "100", "105" and "107". Thus, the highest minimum load in a 3 second period above the target load is 100 - Since the other boxes have values lower than 100 in their 3 second period.

The data is always a curve distribution as you say, that is why in the mini dataset I provided, I reversed the load back down towards 0. to show that while a max load of 107 was reached, the 3 second period from 107 to 81, has a lower "minimum load above target load" than the 100 to 107, 3 second period.

Perhaps I can simplify my goal further with the following sentence.

What is the maximum load that was maintained at or above the target load for 3 consecutive seconds?


I hope I have cleared up any confusion and not made it worse!

 

[etaf]

Still clarifying , but if my assumption is correct then this may do
=AND($A2>3000,$B2>=$C$2,$B2=MAX($B$2:$B$15))
BUT you would need to know the full range of results to get the max - is that easy ? or does it need to be automated

So assuming that all we are doing is making sure that A2 is greater than 3000 - or we could add a greater or = >=
and that B2 is greater or equal to the LOAD target
And we then want the MAX in the list which meets those conditions

i have just shown the conditional formatting formula in column D - its not necessary to show this for the conditional formatting to work
BUT so you see the logic

Book4
	A	B	C	D
1	Elapsed mS	Load Cell	Target Load	Formula - example
2	1000	0	80	FALSE
3	2000	20		FALSE
4	3000	66		FALSE
5	4000	84		FALSE
6	5000	95		FALSE
7	6000	100		FALSE
8	7000	105		FALSE
9	8000	107		TRUE
10	9000	93		FALSE
11	10000	81		FALSE
12	11000	74		FALSE
13	12000	60		FALSE
14	13000	33		FALSE
15	14000	3		FALSE
Sheet2

Cell Formulas
Range	Formula
D2:D15	D2	=AND(A2>3000,B2>=$C$2,B2=MAX($B$2:$B$15))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C15	Expression	=AND($A2>3000,$B2>=$C$2,$B2=MAX($B$2:$B$15))	text	NO

This will find ALL the values
as shown in green on your sheet
=AND(A2>=3000,B2>=$C$2,B2>=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2))

 

[etaf]

Added the range where the criteria is met - in green
The max value - in red
The Min value - in yellow
D to G are just for illustration and NOT used in the conditional formatting FYI Only

Book4
	A	B	C	D	E	F	G	H
1	Elapsed mS	Load Cell	Target Load	Formula - Max example	Formula - ALL Meet MIN example	Min value	Min	Max
2	1000	0	80	FALSE	FALSE	FALSE	81	107
3	2000	20		FALSE	FALSE	FALSE	81	107
4	3000	66		FALSE	FALSE	FALSE	81	107
5	4000	84		FALSE	TRUE	FALSE	81	107
6	5000	95		FALSE	TRUE	FALSE	81	107
7	6000	100		FALSE	TRUE	FALSE	81	107
8	7000	105		FALSE	TRUE	FALSE	81	107
9	8000	107		TRUE	TRUE	FALSE	81	107
10	9000	93		FALSE	TRUE	FALSE	81	107
11	10000	81		FALSE	TRUE	TRUE	81	107
12	11000	74		FALSE	FALSE	FALSE	81	107
13	12000	60		FALSE	FALSE	FALSE	81	107
14	13000	33		FALSE	FALSE	FALSE	81	107
15	14000	3		FALSE	FALSE	FALSE	81	107
Sheet2

Cell Formulas
Range	Formula
D2:D15	D2	=AND(A2>3000,B2>=$C$2,B2=MAX($B$2:$B$15))
E2:E15	E2	=AND(A2>=3000,B2>=$C$2,B2>=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2))
F2:F15	F2	=AND(A2>=3000,B2>=$C$2,B2=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2))
G2:G15	G2	=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2)
H2:H15	H2	=MAXIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C15	Expression	=AND($A2>=3000,$B2>=$C$2,$B2=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2))	text	NO
A2:C15	Expression	=AND($A2>3000,$B2>=$C$2,$B2=MAX($B$2:$B$15))	text	NO
A2:C15	Expression	=AND($A2>=3000,$B2>=$C$2,$B2>=MINIFS($B$2:$B$15,$B$2:$B$15,">="&$C$2))	text	NO

 

[AJPlant]

Thanks very much for your responses Etaf. Apologies for the delayed reply I have been moving house in the last week and so have been very busy! I will have a look at your formulas and plug them into one of the actual data sets I have and see if it works as intended.

Getting the Max is easy enough, doesn't necessarily need to be automated.