0

I need to to find the minimum Turnaround Time (TAT) value for each month. The data set spans a total time frame of 3 years and contains 1400 data points.

Currently I make use of the 'filter' to narrow down to the month and then take the minimum value, not hard, but I would like to have this automatic by using a Formula.

Any help would be much appreciated

4 Answers 4

1

I would use a Pivot Table for this.

Say you have this data:

enter image description here

Click inside the table, then click Insert --> PivotTable

It will select the contiguous (non-breaking) range for you, click OK.

It will create a new sheet. Here is where the fun starts.

A new window will appear to the right called "PivotTable Field List." Drag the fields like shown:

enter image description here

Then, click inside the table on any date, then click "Group"

enter image description here

Select "Months" and "Years" then click OK. Now the data is grouped by the year-month like shown:

enter image description here

Go to the PivotTable Field list, click on "Sum of TaT" and instead of SUM, then click "Value Field Settings". Then change the "Summarize Values By" from Sum to Min like so:

enter image description here

Now it shows your min TaT for each month.

enter image description here

Tada! PivotTables makes life EASY :)

0

You can try =min(enter range of numbers).

if you want all of January then sort document by month, then create this function where you want the answer to appear. Click and drag over the TAT column and it will return the lowest value for Januarys. Unfortunately this will sort all three years of January data together. If you want individual January's then create one for each of the years. Duplicate this for each month.

Hope this helps.

1
  • The problem is that it will take up alot of space, 36 columns... and this file is always expanding. Iv found out about pivot table, this may prove to be the answer.
    – Lyulph
    Feb 5, 2013 at 0:43
0

In your output sheet, put the list of month in column A. Input it as dates (i.e. enter 1/1/12, 2/1/12 and then drag it down for 36 rows) and format it so it only shows the month and the year. Then, place the following formula in column B:

=MIN(IF(((MONTH(Sheet1!$A$2:$A$1400)=MONTH(A1))*(YEAR(Sheet1!$A$2:$A$1400)=YEAR(A1))),Sheet1!$B$2:$B$1400,2^15))

Note: This is an array formula, i.e. you need to enter in with Ctrl-Shift-Enter!

This formula assumes, that your date is in column A in Sheet1 and the TAT is in column B. Adjust the as neccessarry - and then copy the formula down.

0

Or, use the slightly clunky “database” minimum (DMIN) function.  Set up your data as a “database”.  (All that means is that they must have a header row.)  Then set up a criteria block, which uses (some of) the same headers, and then specifies relationship criteria (=, >, etc.) below them.  It took me a couple of minutes to realize that you could have multiple criteria columns for the same field; this is necessary if you want an AND conjunction.  (Multiple constraints in the same criteria column are joined with OR, which is useful with =; e.g., =Simon OR =Garfunkel.)

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .