Need help with weighted conditional averages

[culb]

I'm trying to calculate the average Length of Stay for a healthcare building based on diagnosis. The problem I'm running into is that some diagnoses are blank and therefore the average calculation is returning a #DIV/0. So I'm unable to give an average for that class of diagnosis(e.g., ortho, cardiac, etc...). I'm attaching the data from the spreadsheet with all HIPAA data removed.

Also, our network is currently using Excel 2000. Otherwise I'd have gone with AVERAGEIF.

As a bonus, if anyone can tell me how to do this based on payer AND diagnosis, I'd be eternally grateful. As of now my idea is to separate each payer into its own tab. Thanks!

[JohnTopley]

Try this - sets value to 0 if no data

=IFERROR(AVERAGE(IF(Admissions!A3:A$1018=A3,Admissions!B3:B$1018)),0)

[JohnTopley]

I have put formula in the attached: does this meet your need?

[shg]

Pivot table?

Row\Col	I	J
2	Average of LOS
3	Diagnosis	Total
4	Arrhythmia (Afib, etc)	17.8
5	Back, complicated	31.8
6	Back, uncomplicated	12.3
7	Cardiac Other	20.8
8	CHF	20.1
9	Colitis	40.0
10	COPD/Emphysema	20.4
11	CVA	35.3
12	Debility NOS	11.0
13	Endocarditis	35.0
14	Fall	22.0
15	GI bleed	52.0
16	GI Other	16.0
17	Hip, complicated	39.3
18	Hip, uncomplicated	17.7
19	Infectious Other	24.2
20	Knee, complicated	32.8
21	Knee, uncomplicated	15.4
22	Neuro Other	12.5
23	Ortho Other	39.0
24	Ortho Other, Complicated	46.3
25	Ortho Other, Uncomplicated	15.0
26	Other (Unlisted)	14.6
27	Pelvic, complicated	31.1
28	Pelvic, uncomplicated	11.5
29	Pneumonia, complicated	22.9
30	Pneumonia, uncomplicated	12.0
31	Pulmonary Other	13.3
32	Renal Failure/Acute Kidney Injury	27.0
33	Renal Other	24.5
34	Sepsis	28.8
35	Shoulder, complicated	49.8
36	Shoulder, uncomplicated	21.3
37	Status Post-CABG	39.7
38	Status Post-myocardial infarction	25.7
39	Subdural hematoma or intracranial hemorrhage	28.4
40	Surgery Other	22.4
41	Syncope/Dizziness/Vertigo	14.8
42	UTI/Pyelonephritis	22.2
43	(blank)
44	Grand Total	25.8

[culb]

JohnTopley:

The formula you put in works perfectly. Thanks!

shg:

I'll work on a PivotTable for the data... I'm not great with those, however. Thanks for the advice!