# Excel Graphs - any experts here?



## Addy (Mar 12, 2010)

Can anyone here familiar with Excel Graphs help me?

I am trying to chart (bar graph) how many unassigned hours we had for both RN’s and RPN’s for each shift, each day of the week.

For example, we would want to know on a Saturday afternoon shift which is from 1500-1900, how many unassigned hours there are for all the Saturdays in the year for RN’s. Same for every shift (there are four shift each day) every day of the week.

We use Excel 2010. I’m wondering the easiest way to do this. I have played around with it for a few hours and don’t seem to be getting anywhere towards a graph that’s very useful. I used to know excel charts very well but it’s been 10 years since I’ve really used them so I’m finding it frustrating.

So for instance, For RN's, Friday June 14th has the following shifts, with the corresponding "unassigned hours":

0700-1500 7.5 hrs
1500-1900 0 hrs
1900-2300 0 hrs
2300-0700 0 hrs

Saturday June 15, 2013:
0700-1500 0 hrs
1500-1900 3.75 hrs
1900-2300 7.5 hrs
2300-0700 10.5 hrs

And so on and so on. I unfortunately have to hand enter the as it's all hand written on charts, but I could manually calculate the hours on all Friday for RN's for example and enter that as a lump sum, but I'd rather list them somehow as an easy way to double check my addition.

I also need to do a chart for RPN's, and if possible have it in the same chart to compare with RN's but the ability to seperate the two if asked.


----------



## none (Jan 15, 2013)

I'd offer to help but I have a strict non-excel policy because it generally a terrible program to do things like this.

If you are interested in using a program that makes your problem trivial to solve I could offer some guidance. You will need to put in some work: http://www.r-project.org/


----------



## Addy (Mar 12, 2010)

Yes, it's not the best program for this type of thing for sure. Sadly, we are married to Office like so many workplaces are. So I'm stuck with Excel.



none said:


> I'd offer to help but I have a strict non-excel policy because it generally a terrible program to do things like this.
> 
> If you are interested in using a program that makes your problem trivial to solve I could offer some guidance. You will need to put in some work: http://www.r-project.org/


----------



## none (Jan 15, 2013)

So what you're saying is that within a single cell it says "0700-1500 7.5 hrs" and you want to isolate the 7.5 as a numeric variable. Is that correct?

If so, then you can hack the column with a text to columns and use a space as the split identifier. 

If not, you'll need to re-write your question as it's not terribly clear.


----------



## brad (May 22, 2009)

Excel should do a good job of this, no problem.

Are you trying to create one bar chart that shows each day of the week (with all the shifts on those days) and then shows the total unassigned hours for the entire year? Or do you want it broken out by week?

If it were me, I would use a separate tab for each month of the year and create a calendar-style table on each of those tabs with all the data, and then I would use the very first tab as a sort of dashboard that shows you the summary charts you need to be able to see the unassigned hours at a glance, either by day of the week or by week of the year.


----------



## Addy (Mar 12, 2010)

You are both correct. Basically what the Directors are trying to determine is what are the "problem" shifts, shifts that are difficult to fill. I am assuming if there's a lot of young single nurses then Friday and Saturday nights and weekends in general would be more difficult to fill (for example).

So I'm envisioning a graph that somehow compares each shift as a separate factor (ie 0700-1500Mon would be one factor).

I like Brads idea to have a summary as well as more detailed tabs/graphs as I think it's a LOT of information to have in one graph - four shifts x 7 days for RN's and the same for RPN's.


----------



## andrewf (Mar 1, 2010)

none said:


> I'd offer to help but I have a strict non-excel policy because it generally a terrible program to do things like this.
> 
> If you are interested in using a program that makes your problem trivial to solve I could offer some guidance. You will need to put in some work: http://www.r-project.org/


Spoken like someone who does not use Excel. I have used both R and excel, and excel definitely has some uses.


----------



## Addy (Mar 12, 2010)

Thanks everyone, got it figured out thanks to a very amazing person (TY!).


----------



## none (Jan 15, 2013)

andrewf said:


> Spoken like someone who does not use Excel. I have used both R and excel, and excel definitely has some uses.


Of course it has uses. It's pie charts are amazing!

You sound like someone who does not use R.

Interesting read: http://scienceblogs.com/principles/2009/03/18/why-does-excel-suck-so-much/

Finance related: http://www.forbes.com/sites/timwors...be-the-most-dangerous-software-on-the-planet/


----------



## andrewf (Mar 1, 2010)

That does not persuade me. People can make dumb mistakes with R, too. I will agree that excel is probably not something people should rely on for serious statistical analysis and storing or maintaining data. That is what databases are for. 

Charts are probably one of the weakest parts of excel, I agree.


----------

