Records and Rows If Statement Question / Help :)

[colerainescotty]

Hi folks, I have a simple excel spreadsheet that I am creating to get better at Excel and formulas for my IT course. I have a spreadsheet with some dummy data related to hospital procedures.

At the moment there are 6 doctors who carry out a procedure. The doctors in theory have to be audited and a report sheet produced. At the moment all of the data in a row relates to a particular doctor. There are various pieces of data I wish to collate for a particular doctor.

Firstly if possible I would like to be able to (for the report) say "Find all rows with Dr. A and calculate total dose of Drug A given to patients"


Here is an example of what I am trying to achieve

Excel Sheet

A B C D
1 Doctor Drug Dose
2 Dr. A 1mg
3 Dr. B 2mg
4 Dr. A 4mg

Is there a formula for me to be able to say in another "reporting" sheet (if row contains "Dr A" then total "drug dose")

If you need anymore information please do not hesitate to reply and I will be happy to answer.

Thanks so much in advance and I may have more questions soon

 

[colerainescotty]

Sorry typo there. It should read 1.5 mg as the average dose. The criteria is the total mg administered divided by the no. of patients who received it. Patients not taking any medication are not taken into account.

Then for the percentage is just the no. of patients that were given any medication so 2 out of the 4 = 50%.

Hope this clarifies and thanks for the help. You certainly are an Excel genius

 

[jtakw]

I assumed you wanted the new calculations to be on "reporting" sheet as before:

Here's what I did:
Format "reporting" sheet Column "Average Dose mg" C2 down as "Number" to 2 decimal places.
Format "reporting" sheet Column "% Given" D2 down as "Percentage" to 2 decimal places.
Change the decimal places as you wish.

As before, I've use up to row # 400 for the formulas, use a higher number of rows than you anticipate you need.

Excel 2010
	A	B	C
1	Doctor	Patient Drug Dose mg
2	Dr. A	1
3	Dr. B	2
4	Dr. A	4
5	Dr. A	0	<- none
6	Dr. A	5
7	Dr. C	7
8	Dr. C	6
9	Dr. C	9
10	Dr. C	4
11	Dr. Who	3
12	Dr. Who	5
13	Dr. A	6
14	Dr. C	8
15	Dr. Watson	1
16	Dr. Watson	2
17	Dr. Watson	5
18	Dr. A	0	<- none
19	Dr. Who	6
20	Dr. B	9
21	Dr. Pepper	7
22	Dr. Pepper	8
23	Dr. A	2
Sheet2

Excel 2010
	A	B	C	D
1	Doctor	Total Drug Dose mg	Average Dose mg	% Given
2	Dr. A	18	3.60	20.00%
3	Dr. B	11	5.50	50.00%
4	Dr. C	34	6.80	20.00%
5	Dr. Pepper	15	7.50	50.00%
6	Dr. Watson	8	2.67	33.33%
7	Dr. Who	14	4.67	33.33%
reporting

Cell Formulas
Range	Formula
B2	=SUMIF(Sheet2!A$2:A$400,A2,Sheet2!B$2:B$400)
C2	=AVERAGEIFS(Sheet2!B$2:B$400,Sheet2!A$2:A$400,A2,Sheet2!B$2:B$400,">0")
D2	=C2/B2

Formulas copied down.

 

[colerainescotty]

Absolutely fantastic, exactly what I was needing I had to change the percentage a little though only to include the percentage of people seen who had been given the medication. I did this with countifs which I did not even know existed but your averageifs gave me the clue. Thanks very much I must say this is certainly teaching me stuff I never knew. I will let you know how I get on

 

[jtakw]

You're welcome.

I'm curious why you had to change the "% Given" formula, as I already excluded who did Not receive medication in the "Average Dose mg" formula.
So the simple "% Given" formula of =C2/B2 should show correct results.

What formula are you using exactly for "% Given", and are you getting different results?

 

[colerainescotty]

No problem, I had to use countifs to count the number of patients treated by a certain doctor. Then use the formula to get %. Eg as above Dr. A gave medication to roughly 71% of total patients rather than 20%. It may be likely that I didn't explain it very well and if so, apologies for that.

Just to confirm I worked out the total no. of patients treated by a doctor. Then worked out the number that received medication. Then divided it and formatted as percentage. I basically added another step of working out how many patients got medication. This is the formula to work out how many got the medication for a particular doctor =COUNTIFS('Patient Data'!E4:E30000,Operators!C5,'Patient Data'!O4:O30000,">0")

OperatorsC5 being Dr. A and Patient Data being sheet 2

Thanks again and sorry for my very bad explanations

 

[jtakw]

Ok, I understand now.

Once again, you're welcome, and it's great that you're learning new formulas and learning how and where to apply them, Great Job!