CODEWITHSUNDEEP
excelequation
Arturs Ivanovs
Arturs Ivanovs

Reputation: 3

Excel - MAX and MIN value by month for a certain item

Can someone help me out with a formula? I have a large database and I`m trying to figure out, how to get the MAX amount used in January 2017 for a product X.

I have found the averages using - AVERAGEIFS(Avg.de.time!E3:E80231;Avg.de.time!A3:A80231;C2;Avg.de.time!C3:C80231;">="&H7;Avg.de.time!C3:C80231;"<="&EOMONTH(H7;0))

Column A - Item no.
Column B - Supplier name 
Column C - Order date
Column D - Receive date
Column E - Delivery time (D-C)

I`ve spent too many hours on trying to figure this out. So I m asking for Help :)

Upvotes: 0

Views: 809

Answers (1)

ruaridhw
ruaridhw

Reputation: 2345

Using array formulae you can rewrite your AVERAGEIFS statement using a conditional array expression as follows:

=AVERAGE(
  IF(
    (
      (Avg.de.time!A3:A80231 = C2) *
      (Avg.de.time!C3:C80231 >= H7) *
      (Avg.de.time!C3:C80231 <= EOMONTH(H7,0))
    ) > 0,
    Avg.de.time!E3:E80231
  )
)

I've just formatted the code to make it easier to see where each of the criteria, criteria_ranges and value_range appear however this will obviously be one long line in your cell.

It's now very simple to swap out AVERAGE at the start with MAX, MIN or another aggregate function with the rest of the formula remaining identical.

=MAX(
  IF(
    (
      (Avg.de.time!A3:A80231 = C2) *
      (Avg.de.time!C3:C80231 >= H7) *
      (Avg.de.time!C3:C80231 <= EOMONTH(H7,0))
    ) > 0,
    Avg.de.time!E3:E80231
  )
)

As this is an array formula, you will need to type it into your Excel cell and hit Ctrl-Enter to make it an array formula. You can check this worked as curly braces {} will appear around the formula.

Upvotes: 1

Related Questions