# How to create “workaround” MAXIFS and MINIFS formulas in Excel?

I am using the following formula to calculate the average daily temperature value in column B (see image below) for each day over a 15-year meteorological record (temperature values are in column B and there are multiple per day). Column A contains the date that the record was taken and column D is a list of unique dates to get the average daily temperatures for.

``````=AVERAGEIFS(R2:R47505,J2:J47505,AN2:AN5965)
``````

I would like to replicate this formula for the daily maximum and minimum but do not have `MAXIFS` or `MINIFS` formulas in my version of Excel. Is there a workaround to create these formulas?

In the image below, I am trying to calculate the maximum daily temperature in column F; for 01.02.2005, this would be -2.7 for example

You can use:

``````=MAX( R2:R47505 *( J2:J47505=AN2:AN47505 ) )
``````

or :

``````=MAX( IF( J2:J47505=AN2:AN47505, R2:R47505 ) )
``````

and :

``````=MIN( IF( J2:J47505=AN2:AN47505, R2:R47505 ) )
``````

Using your screenshot to mock up your data, I changed the formulas to:

``````=MAX( IF( \$A\$2:\$A\$70 = D2, \$B\$2:\$B\$70 ) )
``````

and

``````=MIN( IF( \$A\$2:\$A\$70 = D2, \$B\$2:\$B\$70 ) )
``````

here is what I get:

But are you sure that `=AVERAGEIFS(R2:R47505,J2:J47505,AN2:AN5965)` is giving you what you want?

To get the average for each day, I used:

``````=AVERAGEIFS( \$B\$2:\$B\$70, \$A\$2:\$A\$70, D2 )
``````

I hand checked the values and they are correct.

• Hmm thanks, but still does not work for me – Simba06 Jun 2 at 13:57
• @Simba06 perhaps post a sample of your data with the desired result – Dave Jun 2 at 14:19
• @Dave Thanks that is a good idea, I have updated the question – Simba06 Jun 2 at 14:48
• @simba06 Hmm - I see now what the data look like, but I am concerned that your AVERAGEIFS may not be delivering what you want. I will update and show you - is easier to see with a screenshot. – mark fitzpatrick Jun 2 at 15:19
• I'm glad you got there in the end, but... what a painful experience! :-D – mark fitzpatrick Jun 3 at 6:40