1

I'm looking for some help with an Excel formula for determining the lowest value based upon data in first two columns. I have seen questions regarding basing it off one column, but I'm not sure how to convert that into a two column data range formula. I have Day, Interval, and Time columns (A, B, C) and am looking to find my fastest interval for each day/interval set. For example: 

Day   Interval   Time
1/1   100         :55
1/1   100         :52
1/1   100         :54
1/1   200        2:40
1/1   200        2:38
1/5   100         :50
1/5   100         :56
1/5   100         :58
1/5   200        2:39
1/5   200        2:36
1/5   200        2:40

I'm looking for a formula that would be able to determine my fastest times for EACH of the intervals (100 and 200) and EACH of the days in a chart format as shown below: 

      Interval
Day    100   200
1/1    :52  2:38
1/5    :50  2:36

I need to avoid using VBA since the version of Excel I'm using (Mac for Excel 2008) does not have that function. There will be a growing number of data points however, the number or formatting of columns will not change. Ideally, I'll be reviewing just the chart immediately above to determine if I'm getting faster over the offseason period.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jess K
  • 11
  • 2

2 Answers2

1

A PivotTable would seem ideal for this:

SO26941633 example

pnuts
  • 58,317
  • 11
  • 87
  • 139
1

A pivot table would solve a lot of problems and provide additional functionality that you may not have considered. In any event, here is the standard formula solution that you requested.

     enter image description here

The standard formula in F2 is,

=MIN(INDEX($C$2:$C$99+(($A$2:$A$99<>$E2)+($B$2:$B$99<>F$1))*1E+99,,))

Fill right and down as necessary.