Saturday, February 14, 2015

Excel - Analyzing Filtered Set Of Data

I wanted to calculate the average and Standard deviation etc of a filtered set of data.

See the cell F4 in this sheet where the average of a set of cells is calculated with 2 filters. 


See cell H4 where Standard deviation is calculated on a filtered range and cell I4 calculating the same STD using the normal way.
(I got the solution from this site.)

If you want to count the count of cells having some text strings "abc" in them (Note that I say having a string and not cells having value "abc", meaning I am referring to cells having values "abcpqrs" also)

then use this formula countifs(a1:d5, "*abc*)

Use of sumproduct to calculate sum of certain cells across multiple columns from a rectangular array of rows and columns based on multiple filter conditions 



  • Set on column headers and 
  • set on values in other columns 

is shown in the same link in the "sumproduct" sheet, cell D9.


If you want sumproduct to include a case of pattern matching (substrings) then see cell E9 in the same sumproduct sheet.


Using Dynamic Sheets In Microsoft Excel

http://www.ozgrid.com/Excel/named-ranges.htm Name Manager is in Formulas tab.

Formula For Finding Maximum Value In Excel Subject To a Criteria In Another Column
=MAX(IF($B$20:$B$470=1,$E$20:$E$470))

Finds the max in column E from those rows where column B = 1.

I think the formula doesn't work if the values are in another sheet.

Additional reading:

No comments:

Post a Comment

Popular Posts

Featured Post

Whom Do We Trust

I came across this: APNews being the trusted source of news for half the world.  And there is Truth Social which also is read and trusted by...