Excel logic find the most recent day that is also a day of the work (make logic non-exclusive)-CodePudding

I am using Excel logic to try and create a seasonal naive forecast.

I am looking at Column G and then specifying that I only want the rows that are for 9pm, which is the cumulative total sales ending for that day. I am also specifying that I want the maximum date in the file because I only want the total sales for the most recent date. The final condition is that I want the date to be Friday. The problem is, this code only works if I type in "Monday" since the most recent date is also Monday (since the data is updated through end of day yesterday.

How can I make these conditions not-exclusive? I don't want to find the most recent day WHEN it is a Friday, I want to find the most recent Friday, whenever that was.

Here is my attempt so far. It returns the most recent day and a 0 if the most recent day is not Friday. But that's not what I'm looking for, unfortunately.

=AVERAGEIFS(All!$G$1:$G$2593,All!$E$1:$E$2593,"9pm" All!$C$1:$C$2593, MAX(All!$C$1:$C$2593),All!$D$1:$D$2593,"Friday")

I've also discovered that AVERAGEIFS and SUMIFS does the same thing

=SUMIFS(All!$G$1:$G$2593,All!$E$1:$E$2593,"9pm" All!$C$1:$C$2593, MAX(All!$C$1:$C$2593),All!$D$1:$D$2593,"Friday")

CodePudding user response：

You should use a combination of MAXIFS and INDEX/MATCH.

Something like this:

=INDEX(All!$G$1:$G$2593,
       MATCH( MAXIFS(All!$F$1:$F$2593,All!$D$1:$D$2593,"Friday"),
       All!$F$1:$F$2593,0)
      )

The MAXIFS function wil return the maximum value from the Date_Time column, where the day is "Friday". This will be the latest friday with a timevalue of 21:00. The MATCH function will return the rownumber from this Date_Time value in the Date_Time column. The INDEX function then returns the value with the corresponding rownumber in the Cumm_$ column.