Change number of rows referenced in a list when draggin the formula - Microsoft Community

i'm using {=small(abs(a2:a6-transpose(a2:a6)),rows(a2:a6)+1)} formula difference in days between 2 closest dates in list of dates. i need able have function automatically change number of rows being referenced when drag down column has multiple lists separated blank.

for example:

date                difference between 2 closest dates in list

mm/dd/yy      = formula

mm/dd/yy

mm/dd/yy

mm/dd/yy

mm/dd/yy

(blank)            = formula

mm/dd/yy

mm/dd/yy

mm/dd/yy

mm/dd/yy

(blank)             =formula

mm/dd/yy

mm/dd/yy

mm/dd/yy

so when drag down formula, want adjust referencing 5 rows 4 rows 3 rows, etc. automatically.

hi,

we've noticed using small and transpose functions in excel 2010. small function returns nth smallest value in data sheet. transpose function lets switch data column rows, or vice versa. however, your windows 10 question more complex typically answered in microsoft answers forums. better suited pro audience on technet. to accurate resolution, please post question in the technet windows 10 networking forum.

let know if have other concerns.

Office / Excel / Windows other / Office 2010