Forum Discussion
How to create a table that looks for a min and max value.
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.
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.
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.
Please see attached file.
