Dynamic formula to determine MAX $$ values (based on year & quarter)

[skydivetom]

Experts:

I need some assistance with creating a dynamic formula which will determine the MAX value based on a date range (year/quarter in a summary matrix).

Please see attached XLSX which contains some sample data as well as the details... see summary below.

What I Currently Have:
- Dates in column A
- $$ values in column B
- MAX $$ values for each quarter (Q1-Q4 for 2019 and Q1-Q4 for 2020) in column C
- Summary matrix in cell range F1:H5 where I display the MAX values for each of the (currently) 8 quarters... linked to the formulas in column C

What I What to Change/Achieve:
- Get rid of the MAX $$ calculations in column C where each quarter is based on a different number of "records" (dates).
- Add a *dynamic* formula in G2:H5 which will lookup the column header (e.g., "2019" or "2020") in G1 and H1 AND the row header (Q1:Q4) in F2:F5...
- ... and then, based on the intersection of, e.g., "2019 & Q1", automatically determine the date range (i.e., cells A2:A6) ...
-... and then determine the MAX value for those dates.

How can this be accomplished?

Thank you,
EEH

[CARACALLA]

F2=IFERROR(AGGREGATE(14,6,$B$2:$B$45/(YEAR($A$2:$A$45)=F$1)/($C$2:$C$45=$E2),1),"")

Copy across and down


without quarter column


F13=IF($I13<>"",AGGREGATE(14,6,$B$2:$B$45/(MONTH($A$2:$A$45)>=MONTH($I13&0))/(MONTH($A$2:$A$45)<=MONTH($J13&0))/(YEAR($A$2:$A$45)=F$12),1),"")

copy across and down

[JohnTopley]

in F2

Please Login or Register  to view this content.

[skydivetom]

CARACALLA -- thank you... your solution is perfect!

John -- your alternative solution works well, too.

I appreciate both of the solutions!!

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[skydivetom]

Hello Czeslaw -- thanks for chiming in.

I'm not too familiar with PowerQuery... but I'm willing to consider it as well.

For testing purposes only, I have done the following:
- Expanded the row source (range) down to row #46.
- Added a new date within 2020-Q4.
- Added $1000 as it new value... this should have replaced the 2020-Q4 value; however, it did NOT.

Not sure if I missed something but expanding the table range should automatically pick up any MAX values (w/o having to modify potential PowerQuery code). Same applies when I create a new year, etc.

Again, I'm willing to consider it but thus far, the other 2 solutions work great while the PowerQuery appears to require additional settings/modifications.

[Czeslaw]

Data--->Refresh All

[skydivetom]

I appreciate the info. While this process works, I (as a user) have NOT been "converted" to the PowerQuery process.

- Refresh should be automatic (IMHO)
- Refresh changes the format (e.g., narrow column width).

Again, thank you for offering this alternative, but the old-fashioned method appears to work better for me.

[Czeslaw]

Formula

Please Login or Register  to view this content.

[skydivetom]

Czeslaw -- this is a nice alternative. It doesn't require the helper column (Q1-Q4).

Thank you,
Tom