Date Math in Large Column

[Al Halowell]

Hello!

I have a huge spreadsheet (32000 rows). I have done a
subtotal on part number. This does a sum on several other
columns of values.

There is a date column (B) with a date for each
transaction for each part number. I am trying to come up
with a way to determine the number of months over which
the transactions occurred for each part number. The
problem is that each subtotalled "group" is a different
number of rows. I started to manually feed in this
formula and then modify it for each group, but this could
take days.

=(max(b2:b20)-min(b2:b20))/30

This gives me an approximate number of months that passed
within that range of cells.

Is there a way to do this in an easier fashion?

Any/all help would be greatly appreciated.

Thanx!
Al Halowell
Tempe AZ

 

[Ken Wright]

Have you considered a Pivot Table. You could then display your data by Part Number and then Date.
This would give you the date spread for each part number. You can even group by Months or Qtrs or
Years if you wish.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm