Averaging formula assistance

[zmbaker]

I have an average formula set up. Is there a way to exclude one of the cells while still leaving a number in the cell.

For example: 15,20,17,18,55,14,12
I want to calculate the average for the following numbers but it appears that 55 is an out lying number. Is there a way to create a drop down box or something that say calculate the average but exclude the 55 in the calculation?

[TMS]

You could sum the values and subtract the maximum value and then divide by the count minus 1


Regards, TMS

[zmbaker]

I considered that but it could also be the opposite way and a number could be too low compared to the others. I am trying to find a way to apply this to an entire worksheet that is a template for future use as well as current.

[MrShorty]

If you are willing to add a helper column, here's how I do this:

1) In an adjacent column, enter a "toggle" value that I use to indicate whether or not to include the value in the average. I tend to use 1 or 0, but it could also be y/n or whatever you fancy.
2) My average then becomes =SUMIF(helper_column,1,data_column)/countif(helper_column,1)

[zmbaker]

I understand what you are trying to say but I cant figure out how to write the sumif formula because it returns a too many arguments error message. I have attached an example of what i am working with.
Capture.PNG

[MrShorty]

Help file for the SUMIF() function: http://office.microsoft.com/en-us/ex...in=HP005204211

It is kind of hard to work from a picture. Am I reading this properly, that not only do we need to figure out a conditional average, but we also need to deal with data in different blocks? I would guess that your "too many arguments" is from trying to build the function around having data in 3 different columns. The first thing I would do with this data is rearrange it so it is all in one column (or row) rather than discrete "blocks".

[zmbaker]

Here is a text example of what i am looking for. I am looking to apply this formula to one row at a time.
A1 B1 C1 D1 E1 F1 G1 AVG
Air Conditioner $119.00 y $119.00 y $109.00 n

[MrShorty]

Then, instead of using helper columns to the right of each data point, use a helper row below the desired data:

Air conditioner -- 119 -- 119 --- 119
include in ave -- y --- y --- y

[TMS]

Some examples:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Values	Include	Average			15,20,17,18,55,14,12
2	15	y	16.00000	=SUMIF(B2:B8,"y",A2:A8)/COUNTIF(B2:B8,"y")
3	20	y	21.57142857	=AVERAGE(A2:A8)		15,20,17,18,55,14,12	15	20	17	18	55	14	12
4	17	y	16.00000	=SUMPRODUCT(--($B$2:$B$8="y"),(A2:A8))/SUMPRODUCT(--($B$2:$B$8="y"))		16	y	y	y	y	n	y	y	=SUMIF(G4:m4,"y",g3:m3)/COUNTIF(g4:m4,"y")
5	18	y
6	55	n
7	14	y
8	12	y