Hi,


I have a spreadsheet to calculate doctor payout in a medical establishment.

The objective is to accurately pay the doctors for services both scheduled and emergency cases.

The pay for the doctors is based on their planned medical appointments and walk in and emergency cases.

The file uploaded to skydrive consists of 3 worksheets:https://skydrive.live.com/redir?resi...fgDK5 R_j81vk

Worksheet - Hosp Scheduled work

Worksheet - Actual including emergencies

Worksheet - Payout to hosp staff

Note worksheet Hosp Scheduled work and Payout to hosp staff are equal.

The main worksheet consisting of the formula is on Payout to hosp staff worksheet.

Question 1

In cell G2- I would like to know if the doctor did the medical procedure they were planned to do. A yes/no formula would be great. For example, if there is a value in H2, yes. H2 does not have to equal F2 since the doctor may charge more if the procedure took longer or less if cut short due to an emergency case walk in.

In cell H2 of Payout to hosp staff worksheet, I have the following formula to extract below. However, this formula is not working for me. I am receiving an error message. The formula is to obtain the charge(cell c2) from Actual including emergencies using doctor name(cell A2) and medical procedure code (b2) as identifiers.

=SUM((Actual including emergencies!$A$2:$A$16732=A2)*(Actual including emergencies!$B$2:$B$16732=C2)*(Actual including emergencies!$C$2:$C$16732))

Question 2

In cell I2 – if they did not do the medical procedure as planned – what did they do?—however, this may need extra rows to be added below as each doctor could be planning to do 4 medical procedures and they actually did 10 due to emergency walk ins and ambulances

In cell J2- how much did they charge for medical procedure in cell I2.

Any help would be appreciated.

I have to do this weekly for salary payout for medical personnel. I do this manually now