Averages

[Frankie needs HELP]

I've got a list of patients who have been dignosed with various illnesses (some the same), each patient had a waiting time in which they had to wait until they were admitted. How do I get the average waiting time for the diagnosis?

[Mark W.]

Use a PivotTable. How is your data organized?

[Aladin Akyurek]

On 2002-04-05 08:30, Frankie needs HELP wrote:
I've got a list of patients who have been dignosed with various illnesses (some the same), each patient had a waiting time in which they had to wait until they were admitted. How do I get the average waiting time for the diagnosis?

One way:

Make a unique list of diganoses, lets say, in column F from F2 on.

Lets suppose that the diagnosis data is in A from A2 on and waiting times in B from B2 on.

In G2 enter:

=SUMIF($A$2:$A$100,F2,$B$2:$B$100)/MAX(1,COUNTIF($A$2:$A$100,F2))

Note. It would be nice if you also include some sample data when you post a question.

Aladin

[Frankie needs HELP]

Waiting Diagnosis
Time

154 Varicose veins
286 Hernia
246 Tonsillectomy
188 Hysterectomy
18 Bowel cancer
0 Heart Attack
274 Stomach ulcer
222 Stomach ulcer
223 Hernia
124 Varicose veins
542 Hernia
234 Tonsillectomy
360 Stomach ulcer
2 Appendectomy
1 Heart Attack
324 Hernia
300 Varicose veins
2 Heart Attack

This is my data. I have to find the average waiting time for the diagnosis.

[Duane]

Frankie - are those times in minutes, hours or...??

Aladin - I too find using SUMIF/COUNTIF a little easier than CSE formulas, good shot.

[Mark W.]

And, here's the PivotTable...

*

Average of Waiting Time
*Diagnosis*	Total
Appendectomy*	2
Bowel cancer*	18
Heart Attack*	1
Hernia*	343.75
Hysterectomy*	188
Stomach ulcer*	285.3333333
Tonsillectomy*	240
Varicose veins*	192.6666667
Grand Total	194.4444444

*

[Aladin Akyurek]

On 2002-04-05 09:03, Frankie needs HELP wrote:
Waiting Diagnosis
Time

154 Varicose veins
286 Hernia
246 Tonsillectomy
188 Hysterectomy
18 Bowel cancer
0 Heart Attack
274 Stomach ulcer
222 Stomach ulcer
223 Hernia
124 Varicose veins
542 Hernia
234 Tonsillectomy
360 Stomach ulcer
2 Appendectomy
1 Heart Attack
324 Hernia
300 Varicose veins
2 Heart Attack

This is my data. I have to find the average waiting time for the diagnosis.

Suppose that the above sample is in A1:B19.

Lets create a diagnosis list in D from D1 on. The following is extracted from your sample, using Advanced Filter:

{"Diagnosis";
"Varicose veins";
"Hernia";
"Tonsillectomy";
"Hysterectomy";
"Bowel cancer";
"Heart Attack";
"Stomach ulcer";
"Appendectomy"}

In E1 enter:

=MATCH(9.99999999999999E+307,A:A)

In E2 enter and copy down:

=SUMIF(OFFSET($B$2,0,0,$E$1-1,1),D2,OFFSET($A$2,0,0,$E$1-1,1))/MAX(1,COUNTIF(OFFSET($B$2,0,0,$E$1-1,1),D2))

This formula-based method is fully automatic. Additionally, you don't have to adjust the formulas when you add more waiting times with associated diagnoses to the data area.

You might want to keep this around. It can be a handy alternative to the Pivot Table approach.

Here is what you get in the result area:

{"Diagnosis",19;
"Varicose veins",192.666666666667;
"Hernia",343.75;
"Tonsillectomy",240;
"Hysterectomy",188;
"Bowel cancer",18;
"Heart Attack",1;
"Stomach ulcer",285.333333333333;
"Appendectomy",2}

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-05 09:32 ]

[Mark W.]

This formula-based method is fully automatic. Additionally, you don't have to adjust the formulas when you add more waiting times with associated diagnoses to the data area.


Aladin

Dynamic ranges can be used to define the extent of the data source of a PivotTable as well. Futhermore, if Excel's built-in Data Form is used to update the data list it automatically handles the range extension -- without the volatility associated with OFFSET.

[ This Message was edited by: Mark W. on 2002-04-05 09:34 ]