Filter function - Return only 1 column

[Barque]

Is it possible to return only 1 specific column using the filter function? I have seen the methods of choosing adjacent columns, but I would like to return only 1 specific column.

For example, I have used the formula =(FILTER(A2:B28,(A2:A28>=I2)*(A2:A28<=J2),"NA")) which returns the below data

Would it be possible to have it only return the "Hi Temp" column?

The full worksheet is below. Many thanks.

 

[Fluff]

If you only want col B just use

=FILTER(B2:B28,(A2:A28>=I2)*(A2:A28<=J2),"NA")

 

[Barque]

Wow, thank you! Thought that I tried that before and couldn't get it to work.

I am trying to get the weekly Hi Temp per week as shown in the table below. To do this I am extracting the start and end dates of the week using:

I then use the formula below to get the MAX Hi Temp for the referenced week.

I am working with 20 years of data and business days only (M -F less holidays), so about 5,000 rows. I am having some performance issues when using a large amount of rows as I copy the formula down, largely because it looks through all rows. Would the offset function be the best solution here?

The formula

Only needs to be =MAX((((FILTER($B$2:B8,($A$2:$A$8>=I2)*($A$2:$A$8<=J2),"NA"))))), and copied down just needs to jump a week so:

=MAX((((FILTER($B$8:$B$15,($A$8:$A$15>=I2)*($A$8:$A$15<=J2),"NA")))))

I cannot seem to find the best way to accomplish this copy down in column K. I tried using helper columns that would instruct the start and end of the array but could not get that to work.

 

[Fluff]

Would the offset function be the best solution here?

Most definitely not, it would be a lot worse. That formula should not take long to calculate even if you copy it down 5000 rows.

 

[Barque]

Is there an easy way to have the referenced array skip to every 7th row? I am only looking at 1 week at a time, so I don't need to look through the entire array. When I copy and paste, it only skips the usual 1 row at a time.

 

[Fluff]

Doing anything like that will very probably slow thing down even more.

 

[Eric W]

Instead of MAX(FILTER have you tried just MAXIFS?

=MAXIFS($B$2:$B$5000,$A$2:$A$5000,">="&I2,$A$2:$A$5000,"<="&J2)

 

[Barque]

Thank you both for the responses. Both work perfectly. I cleaned up some other cells and speed is not an issue.

A problem I am encoutering is my data set is such that is has no values for Holidays. So for example Good Friday, 4/2/2021, is a blank cell. It is still in the range of the week of 3/28/2021 - 4/3/2021. In this case, I need to return the last value in that week - which is the value for Thursday 4/1/2021. I can use index/match to retrieve it from the array, but is there a simple method to return the 1st or last value in a specified time range? In this case, the week of 3/28/2021 - 4/3/2021?

 

[Fluff]

The formula will return the largest value for the week regardless of whether some of the dates are "missing"

 

[Barque]

Here is a real world example.

This is what my data looks like. I have used the MAXIFS and MINIFS to get the data for 12AM and 12PM columns - which always contain the lowest and highest temps.

For my purposes, I need to retrieve the 1st value of the week for the"6AM" column highlighted in green, and the last value of the week for the "6PM" column also in green.

Because of Holidays, I cannot figure out the correct function/formula to retrieve the correct data. For example, we had a holiday on 4/2/2021 - so the correct value for "6PM" for the week of 3/28/2021 - 4/3/2021 would be the value in cell E6, opposed to whatever value is on a Friday with no Holidays.

I essentially need 2 filters. 1st, filter out the weekly range based on columns K and L, then filter the 1st Non Holiday value for the week in Column C, and last Non Holiday value in Column E.

Any suggestions?