90 consecutive problem

[jplougee]

Don't know if anyone can help with this problem. What I am attempting to do is determine Medicaid patient volume for providers. I have two tabs EP encounters and EP Medicaid encounters. I have looked at 90 day consecutive periods for each quarter to determine who meets the 30% threshold however to really capture all the eligible providers I need to be able to look at any 90 day period within the calendar year. That means I have to find the highest 90 day consecutive period Medicaid patient volume which means I need to be able to compare every 90 day period of EP encounters to EP Medicaid encounters. I would also need to be able to identify the 90 day period i.e. 1/1/11 - 4/1/11 for example. I had to strip much of the workbook out as the file was to large. I don't know what if I am attempting to do is feasible in Excel but my availalbe resources are limited and as always short on manpower, any help or suggestions are appreciated!


Thanks,

Jeremy

[kvsrinivasamurthy]

Pl see the attached file with formula for first quarter.

[jplougee]

Thanks for the help. I was able to look at each quarter I just wasn't able to upload the entire workbook. My problem is I need to find the highest 90 day period for each provider of Mediciad volume so I would have to look at every possible 90 day period for them in a calendar year. For example it might be 1/1 to 4/1 or 2/2 to 5/2 and would be different for every provider. Beyond calculating what 90 period is their highest Medicaid volume I would have also know what that date range is. I am not sure if what I want to do is doable.

[MarvinP]

Hi,

I put both list together on a separate sheet and did a pivot table to add the daily counts. Then did some calculations to the right of the pivot to get what I think you want. See if it helps on the attached.

[jplougee]

Thanks Marvin. It wasn't what I was looking for exactly. I need to know by provider so Dr. Smith had x encounters & x medicaid encounters in a consecutive 90 day period that resulted in their hightest Medicaid percentage over the course of a year and what those date ranges are.