Am I allowed more than one question? :)

Maybe someone here knows.
Looking for 3 items from a spreadsheet.

In Column A: Have dates in no particular order for shipments spanning 5
years.
in column B, have shipment dollars for the various dates

A. Looking for the average of shipment dollars for the previous 12
months (looking back from todays date).

If it finds 16 events ocurring during the 12 months, divide the
12 month total by 16.

B. The hignest single value within those 12 months from column B

c. The lowest single value within those 12 months from column B

TIA for all suggestions. Pierre

Pierre,

Enter these using Ctrl-Shift-Enter (They are Array formulas)

=AVERAGE(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))
=MAX(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))
=MIN(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))

HTH,
Bernie
MS Excel MVP


"Pierre" wrote in message
oups.com...
Am I allowed more than one question? :)

Maybe someone here knows.
Looking for 3 items from a spreadsheet.

In Column A: Have dates in no particular order for shipments spanning 5
years.
in column B, have shipment dollars for the various dates

A. Looking for the average of shipment dollars for the previous 12
months (looking back from todays date).

If it finds 16 events ocurring during the 12 months, divide the
12 month total by 16.

B. The hignest single value within those 12 months from column B

c. The lowest single value within those 12 months from column B

TIA for all suggestions. Pierre

Bernie Deitrick wrote:
Pierre,

Enter these using Ctrl-Shift-Enter (They are Array formulas)

=AVERAGE(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))
=MAX(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))
=MIN(IF(A1:A1000DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())),B1:B1000))

HTH,
Bernie
MS Excel MVP




Bernie, you are a GENIUS!. Thank you!

Pierre