How to create a table that looks for a min and max value.

[Kukaratza]

Hello all, 

I know basic Excel, but this is way beyond my knowledge. I have a table like this:

Test Case | IDExecution |  IDCSVRow | IDName | Value

8.4.2.1	0	0	Delay limit	802
8.4.2.1	0	0	Iteration	1
8.4.2.1	0	0	Measure reporting delay	58
8.4.2.1	0	0	Verdict	Pass
8.4.2.1	0	1	Delay limit	802
8.4.2.1	0	1	Iteration	2
8.4.2.1	0	1	Measure reporting delay	48
8.4.2.1	0	1	Verdict	Pass
8.4.2.1	0	2	Delay limit	802
8.4.2.1	0	2	Iteration	3
8.4.2.1	0	2	Measure reporting delay	45
8.4.2.1	0	2	Verdict	Pass
8.4.2.1	0	3	Delay limit	802
8.4.2.1	0	3	Iteration	4
8.4.2.1	0	3	Measure reporting delay	62
8.4.2.1	0	3	Verdict	Pass
8.4.2.1	0	4	Delay limit	802
8.4.2.1	0	4	Iteration	5
8.4.2.1	0	4	Measure reporting delay	56
8.4.2.1	0	4	Verdict	Pass

And I need to create a table that looks for the lowest and highest value from the Measure reporting delay and create a table that looks like this.

IDExecution: 0

Row Labels                                                         Max Value | Min Value  

Delay limit	802	802
Iteration	4	3
Measure reporting delay	62	45

This is what it needs to look at:

It needs to find the lowest "Measure reporting delay"  and add the iteration which is on the above line and the Delay limit (above the iteration).

Same process with the highest value for "Measure reporting delay".

All this it's one group of values/numbers assigned to "IDExecution" which in this case is 0, so the same process needs to happen if we have more IDExecutions. So find the lowest/highest value for IDExecution 0 and then for IDExecution 1... etc.

Hope this helps, and thank you in advance for your help. Really appreciated.

Thank you.

[#2]

@Kukaratza 

This formula is in cell F26 in the attached example:

=INDEX(E2:E21,MATCH(1,(B2:B21=D24)*(D2:D21=C28)*(E2:E21=F28),0)-2)

This formula is in cell F27 in the attached example:

=INDEX(E2:E21,MATCH(1,(B2:B21=D24)*(D2:D21=C28)*(E2:E21=F28),0)-1)

In cells G26 and G27 in the above formulas F28 is replaced by G28.

This formula is in cell F28 in the attached example:

=LARGE(IF((B2:B21=D24)*(D2:D21=C28),E2:E21),1)

In cell G28 in the above formula LARGE is replaced by SMALL.

Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

@OliverScheurich 

Hello, I tried to adjust your formula to my current spreadsheet, but i get error messages. I tried to attach the xml file, but it wont allow it, 

So i took screenshots.

This is is for R6.

My rows go from 2 to 133.

This is for R7

and this is for R8

Thank you again!

Andres M.

[#4]

@Kukaratza 

Please see attached file.