Want average sales numbers for last 12 months.

[Pierre]

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]

Pierre,

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

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

HTH,
Bernie
MS Excel MVP

 

[Pierre]

Pierre,

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

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

HTH,
Bernie
MS Excel MVP

Bernie, you are a GENIUS!. Thank you!

Pierre