Multiple Nested Formulas or INDEX/MATCH to Get Year to Date Ranges

Nicole AA

I'm trying to use different formulas (SUM, AVG, MEDIAN) for different healthcare metrics and calculate them using a year to date. I have a column showing the order month # a consult was written (i.e Consult written on 7/19 has Order Month # 7). A current month is in a separate column (Feb is "2"). How do I calculate all consults with an order month # of 7-12 and 1 (consults ordered between July and Jan), if the current month is "2", BUT exclude all orders with and Order Month # of 2-6 (Feb-Jun)?

Any insight would be appreciated

Jgorsich

Does your (presumably fiscal) year always start in July?

The problem is that the month() command considers the year to start in January. So, anywhere you use the month() command do this instead:

=if(month([date]@row)<7,month([date]@row)+6,month([date]@row)-6)

Then in February your new "Month()" result will be 8 and your math become easy - just sum things with a month value of 7 or less.

Watch out for the possibility that if your data spans multiple years you need to account for the year also. A "Fiscal Year" column that does something like this may be necessary as part of a sumifs() function:

=if(month([date]@row)<7,year([date]@row),year([date]@row)+1)

That way July of 2024 shows up as part of fiscal year 2025.

Nicole AA

Thanks for FY solution! This explanation, alongside other INDEX and COLLECT formulas helped me figure out a formula to use in the source file. The final formula used in the source file, for other grids is below. It changes every month.

=IFERROR(INDEX(COLLECT([YTD MONTH RANGES]:[YTD MONTH RANGES], [Last Month]:[Last Month], [Month #]$1, [Order Month Options]:[Order Month Options], [Order MONTH #]@row), 1), "Not Applicable").

Jgorsich

Excellent, glad I could help!