array formula for calculating outliers

**ravinatanson** · 02-01-2013, 07:22 PM

I am attempting to calculate an average without outliers (2 standard deviations from the mean) for a huge amount of data conditional on criteria in another column. Column A has numbers Column B text....

Column A Column B
1 Tank
2 Tank
3 Tank
Blank Row
5 Bin
6 Bin
7 Tank

Column B
TankI need to calculate the average without outliers for column A where column B specifies "Tank"
Any help would be VERY MUCH appreciated. And yes, I know I need to control shift enter.

**benishiryo** · 02-01-2013, 08:38 PM

hi ravinatanson, welcome to the forum. yes you can try the array:
=AVERAGE(IF(B1:B7="Tank",A1:A7))

not sure if this regular formula is available in Mac:
=AVERAGEIF(B1:B7,"Tank",A1:A7)

**ravinatanson** · 02-01-2013, 08:51 PM

Thank you for your reply.

I do know how to write that formula to pull out only the Tank values however I need to also exclude outliers when I do the calculation and I cannot for the life of me figure it out.

**ravinatanson** · 02-01-2013, 08:54 PM

PS the goal is to not have to create an additional column that excludes outliers. I would like to get it all in one formula. I have a massive amount of data and if I had to create new columns it would be 50 of them.