1 / 10

=VLOOKUP(G3,Hospital!A$3:D$10,C3-B3,FALSE)

1. Write an Excel formula to be copied down for cell Patients!H3 to calculate the hospital base charges for the first patient listed. This calculation should not include any additional charges for accommodation status. =VLOOKUP(G3,Hospital!A$3:D$10,C3-B3,FALSE)

gyda
Download Presentation

=VLOOKUP(G3,Hospital!A$3:D$10,C3-B3,FALSE)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 1. Write an Excel formula to be copied down for cell Patients!H3 to calculate the hospital base charges for the first patient listed. This calculation should not include any additional charges for accommodation status. =VLOOKUP(G3,Hospital!A$3:D$10,C3-B3,FALSE) =VLOOKUP(G3,Hospital!A$3:D$10,IF(C3-B3=2,2,IF(C3-B3=3,3,4)),FALSE)

  2. 2. Write an Excel formula to be copied down for cell Patients!I3 to calculate the total additional charges, if any, for accommodation status for this patient’s entire stay at the hospital. These charges can be found in the Accom worksheet. =(C3-B3)*HLOOKUP(F3,Accom!B$2:E$4,3,FALSE)

  3. 3. Write an Excel formula to be copied down for cell Patients!J3 to calculate the total hospital charges for this patient for their entire hospital stay. =SUM(H3,I3)

  4. 4. You have found some potential problems with several of the patient accounts you are analyzing and will need to notify certain departments of account information errors. Write an Excel formula to be copied down for cell Patients!K3 to determine which department, if any, needs to be notified of a potential problem with this patient’s account information. You will base the appropriate course of action based on the following conditions: • If the patient’s ICD-9 code is 123.45 and the hospital stay was entered as OBS (Observation), then indicate that Registration will need to be notified of the potential problem with this patient’s account. • If the patient’sLength Of Stay (LOS) exceeded 2 days and the accommodation code was 1, then indicate that Case Management will need to be notified of the potential problem with this patient’s account information. • Otherwise, indicate that No Further Action needs to be taken. =IF(AND(G3=123.45,E3="OBS"),"Registration", IF(AND(C3-B3>2,F3=1),"Case Management","No Further Action"))

  5. 5. Write an Excel formula to be copied downand across for cell Patients!L3 to calculate the percent of the total Hospital Base Rate charges for the first patient compared to the Hospital Base Rate charges for all of the patients in your list. When copied down, this formula should calculate the percent of total Hospital Base Rate charges for the remaining patients. When copied across, this formula will calculate the percent of Total Accommodation charges for each patient, and finally the percent of Total Hospital charges. =H3/SUM(H$3:H$18)

  6. 6. Write an Excel formula to be copied across for cell Patients!H19 to calculate the average Hospital Base rate for all of the patients listed, rounded to the nearest $100. When copied across this formula should calculate the average accommodation charges and the average total hospital charges (respectively) rounded to the nearest $100. =ROUND(AVERAGE(H3:H18),-2)

  7. 7. Write an Excel formula for cell Patients!N21 to determine TRUE/FALSE if only Patient 18272 had elective surgery. This formula does not need to automatically determine which patient is 18272 and which are not. =AND(D3,NOT(OR(D4:D18)))

  8. 8. Write an Excel formula for cell Patients!N22 to determine the number of patients on the list who had either ICD-9 code 109.10 or 788.81 =COUNTIF(G3:G18,109.1)+COUNTIF(G3:G18,788.81)

  9. 9. Write an Excel formula to be copied down for cell Summary!C3 to calculate the average Total Hospital Charges for the first ICD-9 Code. When copied down, this formula should calculate the average Total Hospital Charges for the remaining ICD-9 Codes. This formula must automatically update if any of the ICD-9 Codes changes. This formula also does not need to include the average number of procedures per year. The calculation should be based solely on the list of patient data given. =AVERAGEIF(Patients!G$3:G$18,A3,Patients!J$3:J$18)

  10. 10. In the Summary worksheet, you will find statistics regarding the average number of procedures of each type performed per year. You will also find the percentage of patients within each ICD-9 Code who have Medicare as their health insurance company. In the Units worksheet you will find the reimbursement rate that Medicare provides for the procedures/surgeries for its patients. Write an Excel formula for cell Summary!E3to be copied down to calculate the daily dollar amount of reimbursement your hospital receives from Medicare. =B3*C3*D3/Units!B$4*Units!B$5

More Related