count and average numbers based off date, not worksheet data is in

Hello,

I have a database of sorts that I am trying to get to function the way I need it to. It's going to be a 12 month cycle, but my attachment, for size sake, is only 3 months. In this instance, I am counting AMAs (medical term for leaving before you are supposed to) and averaging lengths of stay for the AMAs. My formulas right now only count the AMAs listed in the month they are searching in, but as you will notice, sometimes the AMAs occur in the next month, but because the client's admission date was in the prior month, it's being counted in the month of the admission, and not the month the AMA actually occurred. I know I can use a data dump, but the size of this document is actually going to be quite large in its full form, so I'm hoping to find a formula instead. Can anyone help?

Originally Posted by nicki_rae22

Good morning, so no go on my issue anyone?

Working on it....it's going to take me some time, though.

Edit Quick question: Rather than referencing across multiple sheets in the formula(s) in the AMAs sheet are you willing to have helper ranges in AMAs sheet (off to the right out of sight) that summarize the relevant columns from each month sheet? It might make for simpler formula(s).

Yes it makes sense. In fact I already have the count by month AMAs worked out. The figures match expectations.

I only want the average LOS for clients who leave against medical advice (AMA).

I'm still a bit 'foggy' on how to derive those averages. I applied some temporary averaging formulas to length of stay AMA and cannot come up with the figures presented even with guessing. Is there any chance those expected figures are typos" or include non-AMAs ?

Edit I've had some time to rethink this one. Please see my post that follows this one.
OK I think it's finished. LOL!

In the AMAs sheet there is a set of lookup tables. They extract and filter relevant columns from each of the sheets. They are all variations on a base formula

Formula:

Please Login or Register  to view this content.

I retained "AMA" as an error check in the formula even though all of the DOD dates were AMA. I didn't know if your real life data followed that pattern. I also included an AMA lookup table as a cross check as I was building the formulas. Those can be deleted without affecting the formulas.

In J5 and filled across to L5 this for the monthly counts including the look-backs.

Formula:

Please Login or Register  to view this content.

In J6 and filled across to L6 for the average days by month including the look-backs.

Formula:

Please Login or Register  to view this content.

The file is attached.

Edit BTW: You mentioned size concerns above. These lookup tables include liberal use of the INDIRECT function. INDIRECT is volatile. This means that whenever you edit your workbook.....whether it directly affects the lookup tables or not.......INDIRECT is going to recalculate and each formula dependent....directly or indirectly.......upon those lookups is going to recalculate, too. If this becomes problematic (slow workbook) you may have to resort to directly referencing each monthly sheet in the lookup table or directly referencing each monthly sheet in the final formulas.........Ugh! and a real pain to edit!

This is simpler. If you have some control over your data rather referring to and across each monthly summary sheet use a common data table as source data for all summary sheets including the AMAs sheet.

I made a 'data dump' table in 'Main Data' in the attached to demonstrate.

Then the following approach eliminates the need for the lookup tables I proposed earlier, look-backs across multiple sheets and avoids an excess of INDIRECT function calls.

For the counts in J4:L4 of AMAs this formula:

Formula:

Please Login or Register  to view this content.

and for the averages in J5:L5 this formula:

Formula:

Please Login or Register  to view this content.

Additionally if your data in the monthly summary sheets are not already derived from a source table such as 'Main Data' you could also exploit your data source in the following way. Try this array-entered formula in B4 of each monthly summary sheet and fill down and across to U11.

Formula:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Hope this helps.

The file is attached.

I'll Call in the Cavalry.

Edit: Done.

OK, late entry. And, with all respect to FR for the effort he has put in, it is ALWAYS a PitA to analyse data that is spread across multiple sheets. As FR has said, INDIRECT is volatile so will very likely cause performance issues. And complex Array Formulae will just add to the mix. Without using INDIRECT, the solution is not scalable as you will need to have specific formulae for each month column.

So, if this has not been suggested already, why not ditch the idea of having separate sheets for each month and just have one sheet with all the patient data? You can then convert the data to a Structured Table. This will manage the ranges, it will copy formulae, formatting, Conditional Formatting, and Data Validation to each new row. More important, it will allow you to sort the data, filter the data, and Pivot the data. You could then perhaps have a Helper column that determines the month to use for the AMA calculation; that is, if month(DOA)<>month(DOD), use the month(DOD) for your calculation.

A life much simpler ... but not worth pursuing here if that's not something you can live with.

Regards, TMS

OK, have a look at the attached updated workbook. Rather than feed the monthly worksheets from the Data Dump, I have provided a button and code to consolidate the monthly data into a single worksheet, "Consolidated Data". The AMAs worksheet then bases its calculations on the Consolidated Data worksheet.

I have used SUMPRODUCT rather than COUNTIFS and SUMIFS as I believe it is more flexible.

I have also created several Dynamic Named Ranges which are all dependent on the data in column B.

Note that the format of the Consolidated Data worksheet is identical to the monthly sheets, including the headings PLUS a calculated "AMA Applicable Date". The Consolidated Data worksheet must exist; it is not created in the code, although (in theory) it could be.


Regards, TMS

You're welcome. Thanks for the rep.


Replace the existing code with this:

Please Login or Register  to view this content.

Regards, TMS

You're welcome.

Or.

Please Login or Register  to view this content.