Formula in Excel to get additional column values associated with M

[Sunitha]

Hi,

I am trying to automate an Excel report and have to select the Best / Worst
values in 4 test runs. There are 16 scenarios and each scenario has 4
parameters. I am taking the Maximum (for worst case) and Minumum (for Best
case) of the 4 test runs. Each of the scenarios has 5 parameters - Elapsed
time, CPU time, Registry Count, Unique count, File IO.

I am sorting for the Max and Min values only with the elapsed time
parameter. I am looking for a formula which would help me in grabbing the
values for the other parameters for that test run which has the (same column
as that of ) maximum and minimum elapsed time value for each scenario.
Below is the excel spread sheet results that I am trying to populate is
marked in this color


Scenario Description Test Run1 Test Run2 Test Run3 Test Run4 Worst
Case Best Case
SignIn TotalElapsedTime (secs) 1.612 1.853 2.349 2.315 2.349 1.612
CPU Time (secs) 1.374 1.328 2.284 2.324 2.284 1.374
RegistryAccessCount 1893 1893 2810 2768 2810 1893
RegistryAccessUniqCount 15 15 20 20 20 15
Total File IO 8056 7296 12548 13345 12548 8056

For the Worst case, I used the formula =MAX(D2:G2) and similarly for Best
case -> =MIN(D2:G2)

Can someone in the discussion group help me figure out the formula that I
can use to grab the other values for the Max and Min elapsed time for each
scenario. I am using C# for the report automation.


Please let me know if the above question is not clear.

Thanks in advance for any help in solving the above problem,
Sunitha.

 

[Joel]

Something like this

RowNumber = 125
Worst = Range("G" & RowNumber)
Best = Range("H" & RowNumber)

'Get worst time
set c = Range("C" & RowNumber & ":F" &
RowNumber).find(what:=Worst,lookin:=xlvalues,lookat:=xlwhole)
CPU = c.offset(1,0)
AccessCount = c.offset(2,0)
AccessUnique = c.offset(3,0)
FileIO = c.offset(4,0)


'Get Best time
set c = Range("C" & RowNumber & ":F" &
RowNumber).find(what:=Best,lookin:=xlvalues,lookat:=xlwhole)
CPU = c.offset(1,0)
AccessCount = c.offset(2,0)
AccessUnique = c.offset(3,0)
FileIO = c.offset(4,0)