AVG, MED, 75th of filtered list, No Zeros

[Excel1991]

I am attempting to find the average, median and 75th percentile in a list of data that I have, but I would like to exclude zeros from the calculations. Column D has various years (2012,2013,2014) and Column E has salaries. Essentially, I am trying to develop formulas that do these three things:

1. "If the year is 2012, find the average of the salaries, but do not include zeros"
2. "If the year is 2012, find the median of the salaries, but do not include zeros"
3. "If the year is 2012, find the 75th percentile of the salaries, but do not include zeros"

Are there criteria formulas that can accomplish this? or are there formulas I could run if I first filter the data by year?

 

[Saba Sabaratnam]

Good Morning,

My example data has year from A1:A13 and values including zeros from B1:B13. Using the data table, I have developed the following formulas to solve your problems

1. = AVERAGEIFS(B1:B13,B1:B13,"<>"&0,A1:A13,"="&2012)
2. =PERCENTILE.EXC(IF((B1:B13>0)*(A1:A13=2012),B1:B13,""),0.75): Enter as an array formula by entering Shift + Control + Enter
3. =MEDIAN(IF((B1:B13>0)*(A1:A13=2012),B1:B13,"")): Enter as an array formula by entering Shift + Control + Enter

Kind regards

Saba

 

[AhoyNC]

Try

Excel Workbook
	D	E	F	G	H
1	Years	Salaries		Year	2014
2	2012	57144		Average	105,520.67
3	2012	79455		Median	92,222.00
4	2012	121687		75% Percentile	144,092.00
5	2012	0
6	2012	138579
7	2012	148044
8	2012	0
9	2012	133895
10	2012	69187
11	2013	53775
12	2013	0
13	2013	0
14	2013	72360
15	2013	101314
16	2013	121048
17	2014	195962
18	2014	28378
19	2014	92222
Sheet

 

[Excel1991]

Try

	D	E	F	G	H
1	Years	Salaries		Year	2014
2	2012	57144		Average	105,520.67
3	2012	79455		Median	92,222.00
4	2012	121687		75% Percentile	144,092.00
5	2012	0
6	2012	138579
7	2012	148044
8	2012	0
9	2012	133895
10	2012	69187
11	2013	53775
12	2013	0
13	2013	0
14	2013	72360
15	2013	101314
16	2013	121048
17	2014	195962
18	2014	28378
19	2014	92222

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:39px;"><col style="width:117px;"><col style="width:91px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H2 =AVERAGEIFS($E$2:$E$19,$D$2:$D$19,$H$1,$E$2:$E$19,"<>0") H3 {=MEDIAN(IF($D$2:$D$19=$H$1,IF($E$2:$E$19<>0,$E$2:$E$19)))} H4 {=PERCENTILE(IF($D$2:$D$19=$H$1,IF($E$2:$E$19<>0,$E$2:$E$19)),0.75)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Worked like a charm, much appreciated. You saved me lots of time

 

[Excel1991]

I am about 99% that these formulas are working as they should be. In an attempt to check I have taken the data, filtered it by year 2012. Copy and pasted these 2012 values, sorted then by Largest to smallest, deleted the zeros and then took an average to make sure I was getting the same numbers. The numbers for the sample checked out.

I was curious if there are any other ways to perform a "sanity check" on these formulas to ensure they are working accurately.

 

[AhoyNC]

Checking a year manually like you did is probably the best check. If it returns the correct answer for the year 2012, it should work for the other years. Since the 50th percentile should equal the median you could run a check to make sure both formulas (using the 50th percentile) return the same answer.

 

[Excel1991]

Thank you for the response. The formulas appear to beworking well to me. The 50% percentile check also works when assessing themedian so that’s good.


I have another question though. I have 4 columns thatcontain data and then a 5^th column that sums the others up (so E5 isthe sum of (a5, b5, c5, d5) and e6 would be sum of (a6,b6,c6,d6) and soon.At the bottom of my spreadsheet iswhere I used your formulas to search through the columns without counting thezeros.


So at the bottom I have the separate formulas performing thefollowing

• avg of column A,
• avg of B,
• avg of C,
• Avg of D,
• avg of the total column.

I’m trying to wrap myhead around why the sum of these formulas (Average of a,b,c,d) wouldn’t equalthe Average of the total column. In my head I’m curious why the average of mytotal column wouldn’t equal this:


Average of columnA,


+Average of B,


+Average of C,


+Average ofD,


Average of the total column.


Essentially the sum of theaverage for each column does not equal the same as taking the average of thetotal column. Why would this be?

 

[AhoyNC]

If you had included the 0 values Sum(Average( column A-D)) would equal the average of the total column.
See the small example below.
If you take the actual average of columns B - D and add them they equal the average of the total column. See row 9

However if you take the average of the columns excluding the 0 values it doesn't. See row 12. Column B & D are the average of only 4 values and column C is the average on only 3 values.

Excel Workbook
	A	B	C	D	E
1					Total
2		1	0	2	3
3		2	5	4	11
4		3	0	0	3
5		0	6	8	14
6		4	7	5	16
7	Sum	10	18	19	47
8
9	Average	2	3.6	3.8
10	Sum of averages	9.4			9.4
11
12	Average excluding 0	2.5	6	4.75
13	Sum of averages	13.25
Sheet

 

[Excel1991]

Im very impressed that you understood exactly what I was talking about just through that paragraph. This is exactly what happened for me. So it makes sense that your row 13 equal E10 correct? Since the total row has no zeros, its denominator would be higher. so B12+C12+D12 would not equal E12 (which would be the same as the 9.4 since there are zeros). It seems like its making sense to me now. I appreciate the visual.

On another note, how did you incorporate the small spreadsheet? May help me in future post to give a clearer picture in what im trying to accomplish. I apologize if this is already addressed somewhere.

 

[AhoyNC]

I use Excel Jeanie to post copies of the sheets and formulas.
https://translate.google.com/translate?hl=en&sl=de&u=http://www.excel-jeanie-html.de/&prev=search

You can find a list of other programs to post. See Part B for list.
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
I have also used the MrExcel HTML marker which works well.
https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813