Search date ranges

[Kinesh]

I hope you can help!


I have a list of doctors names in column A

I have a list of dates in column B

I have a list of drug doses they have given in column C


I want to work out the average drug dose for each doctor for each year time period since the first date recorded on the system.


For example, this could be the first 72 records for the first doctor but the first 120 records for the second doctor if they had done more procedures.


Please help!

 

[Kinesh]

So for doctor A, the first date may be 1/3/2008. I want to know the average dose of drug given from there for each year time period from this date.


Doctor B may start at a different date however, so the date range needs to be calculated from the first time a doctor's name appears. Getting the first date is easy as when the name in the row above is not the same as the current row, that implies a new doctor's records are starting. It's the telling Excel to add 365/730/1095 days etc to this date and then selecting adjacent cells based on that range.

 

[nazmul_muneer]

Kinesh,

At first, welcome to chandoo's forum

you can try the function AVERAGEIF()

If you can provide some sample data, I can help you more easily

 

[Kinesh]

Thanks for the reply


Some sample data would be:


https://docs.google.com/spreadsheet/ccc?key=0Ah3oFxTuITDXdDBOMnI3SXJRci1mY1lFZTQ1V2FnLWc


I would like John Smith's drug average drug doses for year 1 and year 2 from 1/3/10 but Jack Hunter's year 1 and 2 average doses from 3/7/10.


I can't see how to do this with averageif


Thanks!

 

[Hui]

Kinesh


John Smith Yr 1

=AVERAGEIFS($C$3:$C$65,$A$3:$A$65,"John Smith",$B$3:$B$65,">="&DATE(2010,3,1),$B$3:$B$65,"<"&DATE(2011,3,1))


John Smith Yr 2

=AVERAGEIFS($C$3:$C$65,$A$3:$A$65,"John Smith",$B$3:$B$65,">="&DATE(2011,3,1),$B$3:$B$65,"<"&DATE(2012,3,1))

etc


You may want to have a read of: http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/

 

[Hui]

Kinesh


just noticed that you may be using Excel version 97-2003

if so Averageifs wont work


John Smith Yr 1

=SUMPRODUCT($C$3:$C$65,($A$3:$A$65="John Smith")*($B$3:$B$65>=DATE(2010,3,1))*($B$3:$B$65<DATE(2011,3,1)))/SUMPRODUCT(($A$3:$A$65="John Smith")*($B$3:$B$65>=DATE(2010,3,1))*($B$3:$B$65<DATE(2011,3,1)))


John Smith Yr 2

=SUMPRODUCT($C$3:$C$65,($A$3:$A$65="John Smith")*($B$3:$B$65>=DATE(2011,3,1))*($B$3:$B$65<DATE(2012,3,1)))/SUMPRODUCT(($A$3:$A$65="John Smith")*($B$3:$B$65>=DATE(2011,3,1))*($B$3:$B$65<DATE(2012,3,1)))

 

[nazmul_muneer]

Kinesh

You can get result year-wise and doctor-wise

When you change Doctor's name and Year, your result will shown on the basis of Doctor's name and year

Set two dynamic cell

Type in D2 (for Doctor's name) John Smith

Type in E2 (for Year) 2010

in F2, type the formula

=AVERAGEIFS($C$2:$C$64,$B$2:$B$64,">="&DATE(E2,1,1),$A$2:$A$64,D2,$B$2:$B$64,"<="&DATE(E2,12,31),$A$2:$A$64,D2)


Done!

Change the year

Change the doctor's name

 

[Kinesh]

Thanks Nazmul and Hui


The results I would expect for year 1 average for John Smith would be 3.2 (AVERAGE(c3:c22)) and 4 for his year 2 average (AVERAGE(C23:C28)). It should not be possible to calculate a year 3 average as he does not have 3 years of data. I have added these to the sheet above.


I have over 100000 rows of data as above so need to find a way of doing this automatically if possible - I need Excel to find that there is a new name in the first column and then calculating each year's average beginning from the start date from the date next to each new name.


To clarify I don't need to work out what the average was in 2010 but from the doctor's start date e.g. March 2009 for each year after that.


Thanks for your help - it is much appreciated.

 

[nazmul_muneer]

Kinesh

I think, you have observed my formula. If yes, you can get your answer dynamically. Set your required year and doctor name.


On the basis of this formula you can set also date range. Just try......

 

[Hui]

Kinesh,

So change the dates in my formula as well as the cell range from 64 to 100,000

 

[Kinesh]

Dear Nazmul


Thank you for your formula. I can't get this to work.


I've uploaded a part of the actual Excel 2010 sheet at:


https://docs.google.com/open?id=0Bx3oFxTuITDXakxsaE8tY0V3UFk


You can download it by clicking File-Download


If you type 2010 into E2 it does not work.


I also cannot see how the formula knows how to calculate each year time period (e.g. for John Smith, this would be from 1-May-09 to 30-Apr-10 for Year 1, then 1-May-10 to 30-Apr-11 for Year 2, but for Andrew James, Year 1 would be 14-Jun-11 to 13-Jun-12.


Thanks for your help again.


Kinesh

 

[Kinesh]

Dear Hui


I have tried your formula as well and the same issues arise as with my post to Nazmul. Can you help further? Any comments very gratefully received.

 

[Hui]

Kinesh


For 2009/2010 Whole years in

F2:

=SUMPRODUCT($C$2:$C$467,($A$2:$A$467="John Smith")*($B$2:$B$467>=DATE(E2,1,1))*($B$2:$B$467<DATE(E2,12,1)))/SUMPRODUCT(($A$2:$A$467="John Smith")*($B$2:$B$467>=DATE(E2,1,1))*($B$2:$B$467<DATE(E2,12,1)))


Now for the Year 1- 5 averages, H2:L2 Cells

Add a Named Formula:

SDate: [code]=INDEX(Sheet1!$B$2:$B$467,MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$467,0),)

Then in H2:

=SUMPRODUCT($C$2:$C$467,($A$2:$A$467=$D$2)*($B$2:$B$467>DATE(YEAR(SDate)+COLUMN()-COLUMN($H$1),MONTH(SDate), DAY(SDate)))*($B$2:$B$467<DATE(YEAR(SDate)+COLUMN()-COLUMN($G$1),MONTH(SDate), DAY(SDate)-1)))/SUMPRODUCT(($A$2:$A$467=$D$2)*($B$2:$B$467>DATE(YEAR(SDate)+COLUMN()-COLUMN($H$1),MONTH(SDate), DAY(SDate)))*($B$2:$B$467<DATE(YEAR(SDate)+COLUMN()-COLUMN($G$1),MONTH(SDate), DAY(SDate)-1)))[/code]


Copy across


This picks up the first entry of the Name (D2) and uses that as the Start Date (SDate) for the years


Refer: https://www.dropbox.com/s/ywxuwil0zyg2p7i/datasheet.xlsx

 

[Kinesh]

Dear Hui


That is exactly what I wanted, sorry I couldn't explain it better first time!


I just wish I could write such a formula....


Thanks for all your help


Kinesh

 

[Villalobos]

Hello,


I have an another problem with the date searching.

Could somebody offer me a formula which is able to show me only the next date.


Example:


First column: part number (12345)

Second column: delivery date:

2012.07.10,

2012.07.11,

2012.07.12.


In the marked cell I want to see only the first date: 2012.07.10.


Thanks in advance your help!