Averageifs with multiple criteria s

[EVERYDAY]

I am trying to get the average of a month with multiple criterias and I cannot get it come back with the right answer. I do not want to use a pivot table where i have been cutting a pasting the value. But i cannot come up with the correct formula any/all help with this is greatly appreciated. Please see below please let me know where I am going wrong. I need to come back with an average for each month but the number is not matching what I have in my Pivot table or when I filter on just the month and Year.


=averageifs('Pt Data'!$AD2:$AD32241,'Pt Data'!$C2:$C32241,$AH$4,'Pt Data'!$E2:$E32241,AB$7)/averageifs('Pt Data'!AD2:AD32241)/('Pt Data'!$AD2:$AD32241,'Pt Data'!$C2:$C32241,$AH$4,'Pt Data'!$E2:$E32241,AB$7))

Enc - Discharge Year	Enc - Discharge Month Desc	Enc - Patient Type Desc	Enc - MS DRG CMS Geometric Mean LOS
2021​	January	Inpatient	3.3​
2021​	January	Inpatient	5.2​
2021​	January	Inpatient	3.3​
2021​	January	Inpatient	3.9​
2021​	January	Inpatient	2.5​
2021​	January	Inpatient	2​
2021​	January	Inpatient	2.6​
2021​	January	Outpatient	0​
2021​	January	Outpatient	0​
2021​	January	Inpatient	2.7​
2021​	January	Inpatient	4.3​
2021​	January	Inpatient	4.3​
2021​	January	Inpatient	4​
2021​	January	Inpatient	3.3​
2021​	January	Inpatient	7.4​
2021​	January	Inpatient	3.3​
2021​	January	Inpatient	1.7​
2021​	January	Inpatient	4.3​
2021​	January	Inpatient	3.1​
2021​	January	Inpatient	6​
2021​	January	Inpatient	3.3​
2021​	January	Inpatient	4.1​
2021​	January	Inpatient	3.1​
2021​	January	Inpatient	6​

 

[KRice]

Where are the columns that the conditions apply to (I can't tell from your snapshot which column is which)? The use of AVERAGEIFS isn't correct in several cases where AVERAGEIFS isn't receiving the correct number of arguments. Your formula shows several ranges. Which range are you seeking the average of, and what are the ranges/constraints? Here's a list of that I separated out from the formula, but further explanation would be helpful.
'Pt Data'!$AD2:$AD32241
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!AD2:AD32241
'Pt Data'!$AD2:$AD32241
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)

 

[EVERYDAY]

Where are the columns that the conditions apply to (I can't tell from your snapshot which column is which)? The use of AVERAGEIFS isn't correct in several cases where AVERAGEIFS isn't receiving the correct number of arguments. Your formula shows several ranges. Which range are you seeking the average of, and what are the ranges/constraints? Here's a list of that I separated out from the formula, but further explanation would be helpful.
'Pt Data'!$AD2:$AD32241
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!AD2:AD32241
'Pt Data'!$AD2:$AD32241
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)

My apologies I took out the other colunms the ones above are the columns I need to pull the data from. I need to find get the percentage of the last column the "MS DRG CMS Geometric Mean LOS" monthly. I only added some of January's data and not all the months for the post. But the columns above are the only ones that will need to be in the formula. The criterias would be by year (have multiple years), then by Pt type (in or out Patient), then by month. I have tried just by doing the formula like this but the average comes back incorrect. averageifs('Pt Data'!$AD2:$AD32241,'Pt Data'!$C2:$C32241,$AH$4,'Pt Data'!$E2:$E32241,AB$7)

Not sure what to do next, I thought the "Averageifs" would work just like a sumifs???
I hope this helps any/all help in this matter is greatly appreciated.

 

[KRice]

Okay...that helps some, but it still doesn't completely clear things up. Let's go back to the list of items that I pulled from your original formula. There are three ranges, shown here:
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!$AD2:$AD32241

In your last post, you have alluded to four ranges:
"MS DRG CMS Geometric Mean LOS" (presumably this is column AD?)
"January's data" (presumably this is column E?)
"by year" (presumably this is column C?)
"Pt type" (what column?)

Please confirm my guesses regarding the columns where these data are found and fill in whichever one is missing. And for the range that has not been described, where is the cell used for matching (probably the cell specifying "inpatient" or "outpatient")?

 

[EVERYDAY]

Okay...that helps some, but it still doesn't completely clear things up. Let's go back to the list of items that I pulled from your original formula. There are three ranges, shown here:
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!$AD2:$AD32241

In your last post, you have alluded to four ranges:
"MS DRG CMS Geometric Mean LOS" (presumably this is column AD?)
"January's data" (presumably this is column E?)
"by year" (presumably this is column C?)
"Pt type" (what column?)

Please confirm my guesses regarding the columns where these data are found and fill in whichever one is missing. And for the range that has not been described, where is the cell used for matching (probably the cell specifying "inpatient" or "outpatient")?

Enc - Patient Type Desc

 

[KRice]

I understood that "Pt type" referred to the column heading "Enc - Patient Type Desc". My question is...where on the spreadsheet is this column located? Is it column A, B, C, etc? I would like you to confirm the column locations (with column letters) for all four items. And then describe the cell locations used for matching in each of those columns.

 

[EVERYDAY]

A B C D

Enc - Discharge Year	Enc - Discharge Month Desc	Enc - Patient Type Desc	Enc - MS DRG CMS Geometric Mean LOS
2021	January	Inpatient	3.3

=averageifs($D:$D,'Pt Data'!$a:$a,$AH$1,$b2:$b,AB$7, $C:$C,$AA$4)

ah1

2021

aa4

ae4

Month

January

February

March

April

May

June

July

August

September

October

November

December

2021 Total

Inpatient

GMLOS

average for month​

​

​

​

​

​

​

​

​

​

​

​

 

[KRice]

Now I'm more confused. Your original post showed only three ranges and two single-cell references:
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!$AD2:$AD32241
Now you are indicating columns A,B,C,D are used somehow? A better explanation of what your spreadsheet looks like is needed. Based on your original two posts, I guessed that the sheet looks something like this, but I arbitrarily placed [Enc - Patient Type Desc] in column AC and its filtering criteria in AH7 (this needs to be clarified).
But it appears that you really want to operate on information from a 12-month table. That needs explanation too.

As an important side note, it appears that you want to find the average of something called "Enc - MS DRG CMS Geometric Mean LOS". Are you averaging averages? That is normally a bad thing to do because the items being averaged are given the same weighting, while each data point may have been derived from wildly different data sets (e.g., one represents the average of two data points while the other represents the average of 1000 data points). But whether taking the average is meaningful/valid depends specifically on the nature of the data being averaged.

Mrexcel_20220916.xlsx
	C		E																							AB	AC	AD				AH
1	Enc - Discharge Year		Enc - Discharge Month Desc																								Enc - Patient Type Desc	Enc - MS DRG CMS Geometric Mean LOS				Average
2	2021		January																								Inpatient	3.3				3.80
3	2021		January																								Inpatient	5.2				Discharge Year
4	2021		January																								Inpatient	3.3				2021
5	2021		January																								Inpatient	3.9
6	2021		January																							Discharge Month	Inpatient	2.5				Patient Type
7	2021		January																							January	Inpatient	2				Inpatient
8	2021		January																								Inpatient	2.6
9	2021		January																								Outpatient	0
10	2021		January																								Outpatient	0
11	2021		January																								Inpatient	2.7
12	2021		January																								Inpatient	4.3
13	2021		January																								Inpatient	4.3
14	2021		January																								Inpatient	4
15	2021		January																								Inpatient	3.3
16	2021		January																								Inpatient	7.4
17	2021		January																								Inpatient	3.3
18	2021		January																								Inpatient	1.7
19	2021		January																								Inpatient	4.3
20	2021		January																								Inpatient	3.1
21	2021		January																								Inpatient	6
22	2021		January																								Inpatient	3.3
23	2021		January																								Inpatient	4.1
24	2021		January																								Inpatient	3.1
25	2021		January																								Inpatient	6
Pt Data

Cell Formulas
Range	Formula
AH2	AH2	=AVERAGEIFS('Pt Data'!$AD2:$AD32241,'Pt Data'!$C2:$C32241,$AH$4,'Pt Data'!$E2:$E32241,AB$7,'Pt Data'!$AC$2:$AC$32241,$AH$7)

Cells with Data Validation
Cell	Allow	Criteria
AH7	List	Inpatient,Outpatient

 

[EVERYDAY]

Now I'm more confused. Your original post showed only three ranges and two single-cell references:
'Pt Data'!$C2:$C32241 (matches with $AH$4)
'Pt Data'!$E2:$E32241 (matches with AB$7)
'Pt Data'!$AD2:$AD32241
Now you are indicating columns A,B,C,D are used somehow? A better explanation of what your spreadsheet looks like is needed. Based on your original two posts, I guessed that the sheet looks something like this, but I arbitrarily placed [Enc - Patient Type Desc] in column AC and its filtering criteria in AH7 (this needs to be clarified).
But it appears that you really want to operate on information from a 12-month table. That needs explanation too.

As an important side note, it appears that you want to find the average of something called "Enc - MS DRG CMS Geometric Mean LOS". Are you averaging averages? That is normally a bad thing to do because the items being averaged are given the same weighting, while each data point may have been derived from wildly different data sets (e.g., one represents the average of two data points while the other represents the average of 1000 data points). But whether taking the average is meaningful/valid depends specifically on the nature of the data being averaged.

Mrexcel_20220916.xlsx
	C		E																							AB	AC	AD				AH
1	Enc - Discharge Year		Enc - Discharge Month Desc																								Enc - Patient Type Desc	Enc - MS DRG CMS Geometric Mean LOS				Average
2	2021		January																								Inpatient	3.3				3.80
3	2021		January																								Inpatient	5.2				Discharge Year
4	2021		January																								Inpatient	3.3				2021
5	2021		January																								Inpatient	3.9
6	2021		January																							Discharge Month	Inpatient	2.5				Patient Type
7	2021		January																							January	Inpatient	2				Inpatient
8	2021		January																								Inpatient	2.6
9	2021		January																								Outpatient	0
10	2021		January																								Outpatient	0
11	2021		January																								Inpatient	2.7
12	2021		January																								Inpatient	4.3
13	2021		January																								Inpatient	4.3
14	2021		January																								Inpatient	4
15	2021		January																								Inpatient	3.3
16	2021		January																								Inpatient	7.4
17	2021		January																								Inpatient	3.3
18	2021		January																								Inpatient	1.7
19	2021		January																								Inpatient	4.3
20	2021		January																								Inpatient	3.1
21	2021		January																								Inpatient	6
22	2021		January																								Inpatient	3.3
23	2021		January																								Inpatient	4.1
24	2021		January																								Inpatient	3.1
25	2021		January																								Inpatient	6
Pt Data

Cell Formulas
Range	Formula
AH2	AH2	=AVERAGEIFS('Pt Data'!$AD2:$AD32241,'Pt Data'!$C2:$C32241,$AH$4,'Pt Data'!$E2:$E32241,AB$7,'Pt Data'!$AC$2:$AC$32241,$AH$7)

Cells with Data Validation
Cell	Allow	Criteria
AH7	List	Inpatient,Outpatient

My apologies I condensed the example in order to make it easier to understand since a have a very big data sheet. But yes it is averaging averages which is what is needed. But if there is not a formula that will do so then i will continue to get my values from a pivot table. Thanks again for all your help

 

[KRice]

Have a look at post #8 for an example of how the average of column AD is taken subject to three matching conditions. You will need to edit the formula to reflect your actual ranges and locations.

Regarding your comment about whether there is a formula to do so...there is, but details about your worksheet structure are needed to ensure the formula accesses the correct ranges and applies the correct constraints.