Using the dataset and Excel, create a new column titled High ER User with Yes if three or more ER visits otherwise No Duration is measured in days, but 30 day intervals are more appropriate for most reporting purposes Using Excel, create a new column titled Duration Months by converting the duration into 30 day intervals Many times complications and comorbidities are rare therefore, these two negative events are summed together Using Excel, create a new column titled Comps Comorbs by adding complications with comorbidities Many times age is grouped in 10 year intervals Using Excel's VLOOKUP function, create a new column titled Age Group with grouped ages of 21 30 yrs, 31 40 yrs, and so on for 10 year intervals The last age group would be 61 70 yrs Use a tab titled Age Groups for this task Next you will create a pivot table with the data and execute the following (refer to the examples in the resource Data Manipulation Screenshots ) Use High ER User as a filter to obtain two filtered views of the pivot table Summarize the data to get counts of claims, sum of claims and months, and average of procedures, prescribed drugs, ER visits, and complications comorbidities Add a calculated field titled Claims PM to the pivot table This calculated field is the sum of claims divided by the sum of duration months and measures the average claim amount per month (PM) Claims($) Age Procedures Precribed Drugs Emergency Room visists Complications Comorbidities Duration High ER User Duration Months Comps Comorbs Age Grou 179 1 63 IN 4 300 Yes 10 3 61 70 319 59 2 0 120 Yes 4 0 51 60 9310 7 62 17 5 353 No 11 76666667 5 61 70 18727 1 55 0 18 Yes 0 6 0 51 60 453 4 66 4 296 Yes 9 866666667 4 61 70 3243 8 68 334 Yes 11 13333333 4 61 70 599 9 50 26 Yes 0 866666667 1 41 50 2224 6 55 86 No 2 866666667 0 51 60 11 2 53 No 0 0 51 60 ABUOONOGOOKU 689 7 55 No 0 033333333 1 51 60 304 1 DOW NOOOOA 62 282 Yes 9 4 2 61 70 200 4 63 340 Yes 11 33333333 6 61 70 978 9 67 285 No 9 5 13 61 70 O O O O O O O O O O O O O O O O H O O H O OO OO C 330 2 60 OOOO OO OO H OO OO OO OO H NON O N O O 318 No 10 6 10 51 60 642 2 64 347 No 11 56666667 9 61 70 W O P W N B W H O H O P A N W A OK NOW 1247 41 40 No 1 333333333 0 41 50 226 6 64 0 0 No 0 0 61 70 12481 1 69 315 Yes 10 5 0 61 70 369 6 48 13 No 0 433333333 0 41 50 3613 1 57 293 Yes 9 766666667 21 51 60 326 68 111 No 3 7 61 70 252 3 60 60 No 2 6 51 60 UI N W N N DO N OWOH 3902 8 41 82 Yes 2 733333333 3 41 50 116 9 49 No 0 0 41 50 978 8 40 229 No 7 633333333 W 31 40 6345 61 14 5 335 Yes 11 16666667 61 70

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 23, 2024

Using the dataset and Excel, create a new column titled High_ER_User with Yes if three or more ER visits; otherwise No. Duration is measured in

Using the dataset and Excel, create a new column titled "High_ER_User" with "Yes" if three or more ER visits; otherwise "No."
Duration is measured in days, but 30-day intervals are more appropriate for most reporting purposes. Using Excel, create a new column titled "Duration_Months" by converting the duration into 30-day intervals.
Many times complications and comorbidities are rare; therefore, these two negative events are summed together. Using Excel, create a new column titled "Comps_Comorbs" by adding complications with comorbidities.
Many times age is grouped in 10-year intervals. Using Excel's VLOOKUP function, create a new column titled "Age_Group" with grouped ages of "21-30 yrs," "31-40 yrs," and so on for 10-year intervals. The last age group would be "61-70 yrs." Use a tab titled "Age_Groups" for this task.

Next you will create a pivot table with the data and execute the following (refer to the examples in the resource "Data Manipulation Screenshots").

Use "High_ER_User" as a filter to obtain two filtered views of the pivot table.
Summarize the data to get counts of claims, sum of claims and months, and average of procedures, prescribed drugs, ER visits, and complications/comorbidities.
Add a calculated field titled "Claims PM" to the pivot table. This calculated field is the sum of claims divided by the sum of duration months and measures the average claim amount per month (PM).

Claims($) Age Procedures Precribed Drugs Emergency Room visists Complications Comorbidities Duration High_ER-User Duration_Months Comps_Comorbs Age_Grou 179.1 63 IN 4 300 Yes 10 3 61-70 319 59 2 0 120 Yes 4 0 51-60 9310.7 62 17 5 353 No 11.76666667 5 61-70 18727.1 55 0 18 Yes 0.6 0 51-60 453.4 66 4 296 Yes 9.866666667 4 61-70 3243.8 68 334 Yes 11.13333333 4 61-70 599.9 50 26 Yes 0.866666667 1 41-50 2224.6 55 86 No 2.866666667 0 51-60 11.2 53 No 0 0 51-60 ABUOONOGOOKU 689.7 55 No 0.033333333 1 51-60 304.1 DOW NOOOOA 62 282 Yes 9.4 2 61-70 200.4 63 340 Yes 11.33333333 6 61-70 978.9 67 285 No 9.5 13 61-70 O O O O O O O O O O O O O O O O H O O H O OO OO C 330.2 60 OOOO OO OO H OO OO OO OO H NON O N O O 318 No 10.6 10 51-60 642.2 64 347 No 11.56666667 9 61-70 W O P W N B W H O H O P A N W A OK NOW 1247 41 40 No 1.333333333 0 41-50 226.6 64 0 0 No 0 0 61-70 12481.1 69 315 Yes 10.5 0 61-70 369.6 48 13 No 0.433333333 0 41-50 3613.1 57 293 Yes 9.766666667 21 51-60 326 68 111 No 3.7 61-70 252.3 60 60 No 2 6 51-60 . UI N W N N DO N OWOH 3902.8 41 82 Yes 2.733333333 3 41-50 116.9 49 No 0 0 41-50 978.8 40 229 No 7.633333333 W 31-40 6345 61 14 5 335 Yes 11.16666667 61-70