0

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

enter image description here

2

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:

enter image description here

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.

11
  • 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
  • 1
    I'm glad you got there in the end, but... what a painful experience! :-D – mark fitzpatrick Jun 3 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.