Jump to content


Photo

Pivot tables


  • Please log in to reply
2 replies to this topic

#1 Destiny

Destiny

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 29 December 2015 - 01:14 AM

Hello, I am a beginner with Excel. I would appreciate some help with the following questions.
#1. I know how to delete duplicates, but I need to find the duplicates, and then count the number of times they appear in a specific time frame. Here's my situation; I am looking at the total number of patients that come to the emergency room. Some come only once, but others have multiple visits within that month. I need a quick way to sort and capture the total number of visits, the repeat visits, and the total number of visits once (excluding the repeat visits).
#2. My other question is related to the three hospital shifts (07:00 am - 15:30 pm. 15:30 pm- 23:30, & 23:30- 07:00). I need to determine which shift is the busiest. What is the best way to do this on a pivot table or any other Excel function? 
My mentor for this job is not savvy with Excel; she does the work, but it takes forever. I know that there is a better way. Can you help?
Thank you.
Destiny


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 29 December 2015 - 07:30 PM

Hi Destiny,

 

This is certainly possible by using a few advanced skills. I've made up some test data to use as an example:

 

ER1.png

 

You said you needed summaries of the number of first-time visits and the number of repeat visits. To do this, you'll need a formula that counts the number of previous visits by each patient. You can do this by using the COUNTIF function to create a formula like this:

=COUNTIF($B$4:$B4,[Patient Name])-1

 

The COUNTIF function is explained in depth in the video lesson: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

You'll also notice that I've used [Patient Name] in the formula. This is a structured table reference. These are explained in the video tutorial: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

Now that you know how many previous visits each patient has had, you can use the IF function to confirm whether each visit is the patient's first or if it is a repeat visit. The IF function will look like this:

=IF([Previous Visits]=0,TRUE,FALSE)

 

For more about the IF function, see the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

Now that you know whether each visit is the patient's first, you can calculate all of the summaries by using the COUNTIF function again.

To count all first-time visits, the formula will be: =COUNTIF(Data[First Time],TRUE)

To count all repeat visits, the formula will be: =COUNTIF(Data[First Time],FALSE)

 

To calculate the total visits, you can either add the first-time and repeat visits together or simply use the COUNT function.

 

ER2.png

 

Next, you needed to find a way of comparing activity for the three shifts. First of all, I've created a table defining the three different shifts:

ER3.png

 

I also set names for the three Start Time cells, called Shift1, Shift2 and Shift3. This will make it easier to use them in formulas.

Naming cells is explained in the video lesson: Expert Skills Lesson 4-1 Automatically create single-cell range names.

 

The next thing to do is extract only the time component from the Date & Time column. You can do this using the TIME function like this:

=TIME(HOUR([Date & Time]),MINUTE([Date & Time]),SECOND([Date & Time]))

 

For an explanation of the TIME function, see the video tutorial: Expert Skills Lesson 3-14 Understand common time functions and convert date serial numbers to decimal values.

 

Finally, you need to calculate which shift each visit occurs in. You can do this by using IF and AND functions, like this:

=IF(AND([Time]>=Shift1,[Time]<Shift2),1,IF(AND([Time]>=Shift2,[Time]<Shift3),2,3))

 

This is quite a complicated formula, but if you understand IF and AND you should be able to understand it.

It says:

IF the Time is greater than or equal to the start time of Shift 1 AND the Time is less than the start time of Shift 2, return 1

...otherwise, IF the Time is greater than or equal to the start time of Shift 2 AND the Time is less than the start time of Shift 3, return 2

...otherwise return 3.

 

You can find much more about the AND function in the video lesson: Expert Skills Lesson 3-16 Use the AND and OR functions to construct complex Boolean criteria.

 

ER4.png

 

Now that you have the shift numbers for each visit, you can create a PivotChart that will count the number of visits that occurred in each shift and show you a comparison:

 

ER5.png

 

You can find instructions on how to create a PivotChart in the video lesson: Expert Skills Lesson 5-23 Create a pivot chart from a pivot table.

 

I have attached a copy of my example workbook with all of these features applied:

 

Attached File  EmergencyRoom.xlsx   19.07KB   72 downloads

ER6.png

 

I hope this is what you were looking for, but please feel free to reply if you have any more questions about this. You can attach an example of your own data if you'd like me to take a look at it (instructions here).


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Destiny

Destiny

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 30 December 2015 - 03:43 AM

Thank you Jonathan! I'm much appreciative. I will give it a try.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users