Excel formula : how to create a bar chart with time slot

[christianecri]

Hello everyone,


This question is one that I have quite often when I do data analysis at my job :

I am very often working with data concerning time of arrival / time of departure of patients.

I want to do bar graph of the number of patients actually there during each one hour period.

Exemple :

Patient A arrival 8 am departure 12 am

Patient B arrival 10 am departure 2pm

I want a bar value 1 for 8-9, then a bar value of 1 for 9-10 then a bar value of 2 for 10-11 and a bar value of 2 for 11-12 then a bar value of 1 for 12am-1pm and bar value of 1 for 1pm-2pm.

The only way that I have managed to do that so far is to create a column for each time slot and then for each patient (each line on my excel table) with a long "if, if if... formula" depending of course on the arrival time / departure time, plug 1 if patient is there during the time slot or nothing if he was not there, and then do a chart with this raw data.

I was wondering if there was any other way, if there is an excel time function which I could use, specifying the time slot and time of arrival/departure which could directly give "1" if patient was there "" if he was not. or a graph who could directly do that without the 24 column needed to chart a day's worth of activity.


Thank you for your help, any feedback/advice would help me a lot.


Christiane

 

[NARAYANK991]

Hi Christiane ,


Do you think you can implement the technique for conditional formatting explained here :


http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/


Narayan

 

[christianecri]

Hi Narayan,


First, thank you for your input.

But, if I understand the blogpost correctly, the contionnal formatting would replace the least problematic part of my problem. When the data is there, creating the bargraph is not the problem.

My data is like this

Time of arrival Time of departure

Patient A 8 12

Patient B 10 2

Patient C 9 4


I would like to know if I can avoid adding at the right of my data 24 columns with lenghty formulas to calculate if the patient was there during the time slot or not.

I don't think conditionnal formatting can help me visualize my data because I need to ADD the number of patient per time slot (as opposed to visualizing if employees are present during a certain time frame where each employee can be a separate line in a gant chart.)


Thank you,


Christiane

 

[NARAYANK991]

Hi Christiane ,


If you download the sample workbook from the link , you will see that the formulae are very simple !


Yes , you do have to enter them in 24 columns , but the formulae themselves are straightforward ; for example , one of the entries is :

[pre]

10	Start coding	12	26

[/pre]
The formula for the first column is : =IF(AND(F$8>=$D18, F$8<=$E18),"1","")


where F$8 has the hour ( 1 to 24 ) , $D18 is the start value ( 12 ) , and $E18 is the end value ( 26 ).


You don't have to change your data format for the CF ; whatever format you have posted can be used as it is.


Narayan

 

[christianecri]

Hello Narayan,


Well, the reassuring thing for me is that I used exactly this formula.(for patients that arrived and left on the same day because when they arrive at 9pm and leave at 2am, then I use another formula) so ok, I may have exagerated, it is not very lenghty but, I just thought there might be an existing excel formula which would also do the job, or a better way than creating 24 columns before I am able to do my chart.

If I am already doing the most efficient thing there is... well, then that's good I guess


Thank you for your help.


Christiane

 

[SirJB7]

Hi, christianecri!


You can shorten a little bit the formula from this:

=IF(AND(F$8>=$D18, F$8<=$E18),"1","")

to this:

=AND(F$8>=$D18,F$8<=$E18)*1

and in fact you should correct the operator of the second criteria:

=AND(F$8>=$D18,F$8<$E18)*1

so as to avoid including data in an additional range slot (08:00-09:00 should go in 08:00 only and not in both 08:00 and 09:00 as now. Check light blue cells in uploaded file.


Give a look at this file for an example:

https://dl.dropbox.com/u/60558749/Excel%20formula%20_%20how%20to%20create%20a%20bar%20chart%20with%20time%20slot%20%28for%20christianecri%20at%20chandoo.org%29.xlsx


Just advise if any issue.


Regards!