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
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.
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:
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