50 formulas of MS Excel with details
Answers
Explanation:
please mark as a brainlist
Add some values
Example
=SUM(5,6,9)
Result 20
Add values from a range of cells
Example
=SUM(A1:A5)
Result 125
Sum up values from a table reference
Example
=SUM(staff[Salary])
Result $ 945,000
Sum of numbers that meet conditions
Example
=SUMIFS(staff[Salary], staff[Department],"Sales")
Result $ 279,000
Learn more about this formula: SUMIFS formula
Sum of numbers greater than (less than etc.) something
Example
=SUMIFS(A1:A6, A1:A6,">25")
Result 100
Add some values
Example
=SUM(5,6,9)
Result 20
Add values from a range of cells
Example
=SUM(A1:A5)
Result 125
Sum up values from a table reference
Example
=SUM(staff[Salary])
Result $ 945,000
Sum of numbers that meet conditions
Example
=SUMIFS(staff[Salary], staff[Department],"Sales")
Result $ 279,000
Learn more about this formula: SUMIFS formula
Sum of numbers greater than (less than etc.) something
Example
=SUMIFS(A1:A6, A1:A6,">25")
Result 100
Learn more about this formula: SUMIFS formula
Sum of numbers that are currently filtered
Example
=SUBTOTAL(109,staff[Salary])
Result $ 945,000
Learn more about this formula: SUBTOTAL function
Count of numbers that are currently filtered
Example
=SUBTOTAL(103,staff[Name])
Result 13
Learn more about this formula: SUBTOTAL function
Running total in a column, adjacent to original data
Type this formula in first cell and drag down to get running total.
Example
=SUM($A$1:A1)
Result 10
Count of numbers in a range
Example
=COUNT(A1:A6)
Result 6
Count of all values (including text)
Example
=COUNTA(staff[Name])
Result 13
Count of blank values in the input (range or table column)
Example
=COUNTBLANK(A1:A20)
Result 14
Count number of non-blank values
ROWS formula tells how rows are there in a range. You can also use COLUMNS
Example
=ROWS(A1:A20)-COUNTBLANK(A1:A20)
Result 6
Count how many items have met given conditions
Example
=COUNTIFS(staff[Department],"IT")
Result 3
Count how many items begin with given text
* is a wild card. You can use it to match any number of letters. If you want to match a single letter, use ?
Example
=COUNTIFS(staff[Name],"J*")
Result 13
Count how many items end with given pattern
Example
=COUNTIFS(staff[Name],"*n")
Result 4
Count how many items contain given word
Example
=COUNTIFS(staff[Name],"*an*")
Result 3
Average of given numbers
Example
=AVERAGE(staff[Salary])
Result $ 72,692
Learn more about this formula: More about Averages in your analysis
Average of given numbers satisfying conditions
Example
=AVERAGEIFS(staff[Salary],staff[Department], "HR")
Result $ 77,333
Average of positive numbers
Example
=AVERAGEIFS(A1:A10,A1:A10,">0")
Result 25.83
Average of numbers excluding top & bottom 10% values
Example
=TRIMMEAN(staff[Salary],10%)
Result $ 72,692
7 day moving average from daily data
Type this formula in first cell and drag down to get moving average.
Example
=AVERAGE(A1:A7)
Result 25.83
Learn more about this formula: Moving Average in Excel
Weighted average of numbers
A1:A6 contain values and B1:B6 contain weights
Example
=SUMPRODUCT(A1:A6,B1:B6)
Result 2,550
Learn more about this formula: Weighted Average Formula
Median of a range of values
Example
=MEDIAN(staff[Salary])
Result $ 76,000
Most frequent number in a range (MODE)
If your list has multiple MODEs, use MODE.MULT to return all of them as a new list.
Example
=MODE.SNGL(1,2,3,3,2,1,1,5,6,7,3,4,8,9)
Result 1
Statistical quartiles of given values
Use 1 for first quartile, 2 for median, 3 for third quartile. EXC means 0 & 1 are excluded when calculating quartiles.
Example
=QUARTILE.EXC(staff[Salary],1)
Result $ 59,500
90th (or any other) percentile of given values
Example
=PERCENTILE.EXC(staff[Salary],0.9)
Result $ 89,000
Minimum value among a list of numbers
Example
=MIN(A1:A6)
Result 10
3rd smallest (or any other) among a list
Example
=SMALL(staff[Salary],3)
Result $ 59,000
Rank of a number in a list of values
If two numbers share a rank, then the rank will be averaged. Use RANK.EQ to return same rank for both numbers
Example
=RANK.AVG(76000, staff[Salary])
Result 7
Maximum value from a list of values
Example
=MAX(staff[Salary])
Result $ 89,000
2nd largest value in a spreadsheet range
Example
=LARGE(A2:A7,2)
Result 30