Rounding and math formulas
Round to any step, round up and down, weighted averages, ranks and remainders. 69 answers, each checked by running it, with a live example and an exercise.
How to round to the nearest 100 or 1000 in Excel
=ROUND(A2,-2)Use ROUND with a negative number of digits: =ROUND(A2,-2) rounds to the nearest 100, and -3 to the nearest 1000.
How to always round up in Excel
=ROUNDUP(B2/C2,0)Use =ROUNDUP(value,0) to round up to the next whole number, or =CEILING(value,step) to round up to a multiple.
How to calculate a weighted average in Excel
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)Multiply each value by its weight, add them up and divide by the total weight: =SUMPRODUCT(values,weights)/SUM(weights).
How to rank numbers in Excel
=RANK.EQ(B2,$B$2:$B$7)Use =RANK.EQ(value, list) for largest = 1, adding 1 as the third argument for smallest = 1. Lock the list with $.
How to calculate an average without zeros in Excel
=AVERAGEIF(B2:B7,"<>0")Use =AVERAGEIF(range,"<>0"). It averages only the cells that are not zero.
How to multiply a column by the same number in Excel
=B2*$F$1Put the number in a cell and lock it with $: =B2*$E$1, then fill down.
How to round to the nearest 5 in Excel
=MROUND(B2,5)Use MROUND with 5 as the multiple,
=MROUND(B2,5), which rounds halfway values like 17.5 up to 20.How to round to the nearest quarter (0.25) in Excel
=MROUND(B2,0.25)Use MROUND with 0.25:
=MROUND(B2,0.25)turns 7.38 into 7.5 and 7.63 into 7.75.How to round up to a multiple in Excel (CEILING)
=CEILING(B2,C2)Use CEILING with the step:
=CEILING(B2,C2)rounds 130 up to 144 when units come in cases of 24.How to round down to a multiple in Excel (FLOOR)
=FLOOR(B2,C2)Use FLOOR with the step:
=FLOOR(B2,C2)rounds 1,375 down to 1,320 when pallets hold 60.How to round down in Excel
=ROUNDDOWN(B2,2)Use ROUNDDOWN with the number of decimals to keep:
=ROUNDDOWN(B2,2)turns 3.4987 into 3.49.How to remove decimals without rounding in Excel
=TRUNC(A2)Use TRUNC:
=TRUNC(A2)drops everything after the decimal point, so 12.86 becomes 12 and -3.75 becomes -3.How to round to 2 decimal places in Excel
=ROUND(B2*$F$1,2)Use ROUND with 2 as the number of digits:
=ROUND(B2*$F$1,2)rounds the calculated tax to the cent.How to round to significant figures in Excel
=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))Work out the digit position from the size of the number:
=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2)))).How to round prices to end in .99 in Excel
=ROUNDUP(B2,0)-0.01Round up to the next whole number and take off a cent:
=ROUNDUP(B2,0)-0.01.Why don't my rounded numbers add up to the total in Excel
=SUMPRODUCT(ROUND(B2:B6,2))The cells show rounded values but store more decimals, so add the rounded values:
=SUMPRODUCT(ROUND(B2:B6,2)).How to get the decimal part of a number in Excel
=MOD(A2,1)Use MOD with 1:
=MOD(A2,1)returns what is after the decimal point, so 7.75 gives 0.75.How to find the remainder of a division in Excel
=MOD(B2,C2)Use MOD:
=MOD(B2,C2)returns what is left over after dividing B2 by C2.How to convert negative numbers to positive in Excel
=ABS(B2)Use ABS, the absolute value:
=ABS(B2)turns -1,250.40 into 1,250.40 and leaves positive numbers alone.How to make positive numbers negative in Excel
=-ABS(B2)Put a minus in front of ABS:
=-ABS(B2)makes every amount negative, including ones that already were.How to calculate a square root in Excel
=SQRT(B2)Use SQRT,
=SQRT(B2), or raise the number to the power of one half with=B2^(1/2).How to calculate a cube root in Excel
=B2^(1/3)Raise the number to the power of one third,
=B2^(1/3), and use 1/n for any nth root.How to raise a number to a power in Excel
=2^A2Use the caret, as in
=2^A2, or the function form=POWER(2,A2).How to use e (Euler's number) in Excel
=$E$1*EXP($E$2*A2)Excel has no constant e, so use EXP:
=EXP(1)is e and=EXP(x)is e to the power x, as in=$E$1*EXP($E$2*A2).How to calculate a logarithm in Excel
=LOG(A2,B2)Use LOG with the base as the second argument,
=LOG(A2,B2); LOG10 is base 10 and LN is the natural log.How to calculate the antilog (inverse log) in Excel
=10^A2Raise 10 to the log value with
=10^A2, or use=EXP(A2)for a natural log.How to calculate RMSE in Excel
=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7))Square the errors, average them and take the square root:
=SQRT(SUMPRODUCT((B2:B7-C2:C7)^2)/COUNT(B2:B7)).How to calculate MAPE in Excel
=SUMPRODUCT(ABS((B2:B6-C2:C6)/B2:B6))/COUNT(B2:B6)Average the absolute errors as a share of the actuals:
=SUMPRODUCT(ABS((B2:B6-C2:C6)/B2:B6))/COUNT(B2:B6).How to calculate percent error in Excel
=ABS(B2-$F$1)/$F$1Divide the absolute difference by the accepted value:
=ABS(B2-$F$1)/$F$1, formatted as a percentage.How to average numbers in Excel
=AVERAGE(B2:B7)Use
=AVERAGE(B2:B7), which adds the numbers and divides by how many there are, skipping blank cells.How to average the top 3 values in Excel
=AVERAGE(LARGE(B2:B9,{1,2,3}))Take the three largest with LARGE and average them:
=AVERAGE(LARGE(B2:B9,{1,2,3})).How to average excluding the highest and lowest values in Excel
=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)Take the highest and lowest out of the total and divide by two fewer:
=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2).How to average a range that contains errors in Excel
=AVERAGE(IFERROR(D2:D7,""))Turn the errors into blanks, which AVERAGE skips:
=AVERAGE(IFERROR(D2:D7,"")).How to average if another cell is not blank in Excel
=AVERAGEIFS(C2:C8,B2:B8,"<>")Use AVERAGEIFS with "<>" as the condition:
=AVERAGEIFS(C2:C8,B2:B8,"<>").How to average based on criteria in Excel (AVERAGEIF)
=AVERAGEIF(B2:B10,F1,C2:C10)Use
=AVERAGEIF(B2:B10,F1,C2:C10): it averages the values in C on the rows where B matches F1.How to average with multiple criteria in Excel (AVERAGEIFS)
=AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2)Use AVERAGEIFS with the range to average first and one pair per condition:
=AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2).How to calculate a moving average in Excel
=AVERAGE(B2:B4)Average the last few rows with a relative range and fill down:
=AVERAGE(B2:B4)in the third row gives a 3-month moving average.How to average the last N values in Excel
=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))Keep the filled cells and take the last N:
=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))in Excel 365.How to calculate a weighted average with a condition in Excel
=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)Weight and filter in the top, total the weights for the same rows in the bottom:
=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7).How to find the median in Excel
=MEDIAN(B2:B11)Use MEDIAN:
=MEDIAN(B2:B11)returns the middle value, or the average of the two middle values for an even count.How to calculate a median with criteria in Excel (MEDIAN IF)
=MEDIAN(FILTER(C2:C10,B2:B10=F1))Filter the values first, then take the median:
=MEDIAN(FILTER(C2:C10,B2:B10=F1)).How to find the most common number in Excel (MODE)
=MODE.SNGL(B2:B11)Use MODE.SNGL:
=MODE.SNGL(B2:B11)returns the number that appears most often.How to find the most common text value in Excel
=INDEX(B2:B10,MODE.SNGL(MATCH(B2:B10,B2:B10,0)))Turn each value into its first position with MATCH, take the mode, and look it up:
=INDEX(B2:B10,MODE.SNGL(MATCH(B2:B10,B2:B10,0))).How to calculate the average per month in Excel
=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0))Use AVERAGEIFS with a date window for each month:
=AVERAGEIFS($B$2:$B$12,$A$2:$A$12,">="&D2,$A$2:$A$12,"<="&EOMONTH(D2,0)).How to find the maximum value with criteria in Excel (MAXIFS)
=MAXIFS(C2:C9,B2:B9,F1)Use MAXIFS with the range to search first, then each condition:
=MAXIFS(C2:C9,B2:B9,F1).How to find the minimum value with criteria in Excel (MINIFS)
=MINIFS(C2:C8,A2:A8,F1)Use MINIFS:
=MINIFS(C2:C8,A2:A8,F1)returns the smallest value on the rows that match.How to find the minimum value excluding zeros in Excel
=MINIFS(B2:B7,B2:B7,">0")Use MINIFS with ">0" on the same range:
=MINIFS(B2:B7,B2:B7,">0").How to find the second largest value in Excel
=LARGE(B2:B7,2)Use LARGE with a rank of 2,
=LARGE(B2:B7,2), and change the 2 for any nth largest.How to find the largest absolute value in Excel
=MAX(ABS(B2:B6))Take MAX of the absolute values:
=MAX(ABS(B2:B6))in Excel 365, or=MAX(MAX(B2:B6),-MIN(B2:B6))in any version.How to calculate the range of a data set in Excel
=MAX(B2:B8)-MIN(B2:B8)Subtract the smallest value from the largest:
=MAX(B2:B8)-MIN(B2:B8).How to rank within groups in Excel
=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1Count the larger values in the same group and add 1:
=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1.How to rank with a tiebreaker in Excel
=RANK.EQ(B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,B2,$C$2:$C$7,">"&C2)Rank on the main column, then add the number of tied rows that win on the second:
=RANK.EQ(B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,B2,$C$2:$C$7,">"&C2).How to calculate standard deviation in Excel
=STDEV.S(B2:B9)Use
=STDEV.S(B2:B9)for a sample, which is the usual case, or=STDEV.P(B2:B9)when the data is the whole population.How to calculate variance in Excel
=VAR.S(B2:B7)Use
=VAR.S(B2:B7)for a sample, or=VAR.P(B2:B7)for a whole population.How to calculate a z-score in Excel
=(B2-AVERAGE($B$2:$B$9))/STDEV.S($B$2:$B$9)Subtract the mean and divide by the standard deviation:
=(B2-AVERAGE($B$2:$B$9))/STDEV.S($B$2:$B$9).How to calculate the standard error of the mean in Excel
=STDEV.S(B2:B9)/SQRT(COUNT(B2:B9))Divide the sample standard deviation by the square root of the count:
=STDEV.S(B2:B9)/SQRT(COUNT(B2:B9)).How to calculate the coefficient of variation in Excel
=STDEV.S(B2:B7)/AVERAGE(B2:B7)Divide the standard deviation by the mean and format as a percentage:
=STDEV.S(B2:B7)/AVERAGE(B2:B7).How to calculate standard deviation with criteria in Excel
=STDEV.S(FILTER(C2:C9,B2:B9=F1))Filter the values, then take the standard deviation:
=STDEV.S(FILTER(C2:C9,B2:B9=F1)).How to find outliers using standard deviation in Excel
=IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","")Flag values more than two standard deviations from the mean:
=IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","").How to convert Fahrenheit to Celsius in Excel
=(B2-32)*5/9Subtract 32 and multiply by 5/9 with
=(B2-32)*5/9, or use=CONVERT(B2,"F","C").How to convert inches to feet and inches in Excel
=INT(B2/12)&"' "&MOD(B2,12)&""""Whole feet from INT, leftover inches from MOD:
=INT(B2/12)&"' "&MOD(B2,12)&""""turns 71 into 5' 11".How to multiply all the numbers in a column in Excel (PRODUCT)
=PRODUCT(B2:B6)Use PRODUCT:
=PRODUCT(B2:B6)multiplies every number in the range together.How to use pi in Excel
=PI()*B2^2Use the PI function,
=PI(), which returns 3.14159265358979; a circle's area is=PI()*B2^2.How to calculate the distance between two points in Excel
=SQRT((D2-B2)^2+(E2-C2)^2)Use the Pythagorean formula:
=SQRT((D2-B2)^2+(E2-C2)^2).How to normalize data to a 0-1 scale in Excel
=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))Subtract the minimum and divide by the range:
=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7)).How to check if a number is a whole number in Excel
=MOD(B2,1)=0Test whether anything is left after dividing by 1:
=MOD(B2,1)=0returns TRUE for whole numbers.How to calculate quartiles in Excel
=QUARTILE.INC(B2:B11,1)Use =QUARTILE.INC(range, 1) for Q1, =QUARTILE.INC(range, 2) for the median and =QUARTILE.INC(range, 3) for Q3. The last argument picks the quartile, 0 to 4.
How to calculate a percentile in Excel
=PERCENTILE.INC(B2:B11,0.9)Use =PERCENTILE.INC(range, k) where k is a fraction from 0 to 1. For the 90th percentile, =PERCENTILE.INC(B2:B11,0.9).
How to find outliers with the IQR method in Excel
=IF(OR(B2<$G$2,B2>$H$2),"Outlier","")Flag a value as an outlier when it is below Q1 − 1.5×IQR or above Q3 + 1.5×IQR, where IQR = Q3 − Q1. Put Q1 and Q3 in cells, then test each row against the two fences.