Sorting date ranges in excel
Question: How do I sort date ranges or intervalls by size in excel?
Answer: Here are the ranges sorted large to small.
Formula in E2:E11: =MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0) + Ctrl + Shift + Enter
Formula in F2:F11: =INDEX($B$2:$B$11, MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0)) + Ctrl + Shift + Enter
Formula in G2:G11: =INDEX($C$2:$C$11, MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0)) + Ctrl + Shift + Enter
Formula in H2:H11: LARGE(C2:C11-B2:B11, ROW()-1) + Ctrl + Shift + Enter
Download excel sample file for this article.
sorting-date-intervalls.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
LARGE(array,k) returns the k-th largest row number in this data set.
ROW(reference) returns the rownumber of a reference
MATCH(lookup_value;lookup_array; [match_type])
Related posts:
Leave a Reply