Excel Math & Trig functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios.
To convert a measurement in inches to inches and feet (i.e. 53 to 4′ 5″) you can use a formula based on the INT and MOD functions. See illustration below: Formula =INT(A1/12)&”‘ “&MOD(A1)&”””” Explanation In the example shown, the formula in D5 is: =INT(B5/12)&”‘ “&MOD(B5,12)&”””” How this formula works This formula converts a numeric value …
To convert a measurement in feet and inches to inches only (i.e. 4’5″ to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. See illustration below: Formula =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) Explanation In the example shown, the formula in D5 is: =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) How this formula works In the first …
To change negative numbers to positive and leave positive numbers along (i.e. get absolute values) use the built-in ABS function. See illustration below: Formula =ABS(number) Explanation In the example shown the formula in C6 is: =ABS(B6) How this formula works The ABS function is fully automatic. All you need to do is supply a number …
To calculate required currency denominations, given a specific amount, you can build a currency calculation table as shown in the example. This solution uses the INT and SUMPRODUCT functions. Formula =INT((amount-SUMPRODUCT(denoms,counts))/currentdenom) Explanation In the example show, the formula in D5 is: =INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4) How this formula works To start off, the formula in C5 is: =INT($B5/C$4) This formula divides …
To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. Formula =SUMPRODUCT(–(range<>””))=0 Explanation In the example shown, the formula in E5 is: =SUMPRODUCT(–(B5:D5<>””))=0 How this formula works Working from the inside out, this formula contains …
If you want to test a cell to see if it equals one of several things, you can do so with a formula that uses the SUMPRODUCT function. Case study: Let’s say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things …
This tutorials shows how to Round to nearest 5 in Excel. If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits. Formula =MROUND(number,5) Explanation In the example, cell C6 contains this formula: =MROUND(B6,5) The value in B6 is 17 …
This tutorials shows how to Round to nearest 1000 in Excel If you need to round a number to the nearest 1000, you can use the ROUND function and supply -3 for number of digits. Formula =ROUND(number,-3) Explanation In the example, cell C6 contains this formula: =ROUND(B6,-3) The value in B6 is 1,234,567 and the …
This tutorials shows how to Round time to nearest 15 minutes in Excel. To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. Formula =MROUND(time,”0:15″) Explanation In the example shown, the formula in C6 is: =MROUND(B6,”0:15″) How this formula works MROUND rounds to …
This tutorials shows how to Round by bundle size in Excel. To round up to the next bundle size, you can use the CEILING function which automatically rounds up away from zero. To round up to the next bundle size, you can use the CEILING function which automatically rounds up away from zero. Formula =CEILING(number,bundle)/bundle …
This tutorials shows how to Round a price to end in .99 in Excel If you need to round prices so that they ended in the nearest, .99 value, you can use the ROUND function then subtract .01. Formula =ROUND(price,0)-0.01 Explanation The formula shown in the example is: =ROUND(B6,0)-0.01 With the value in B6 of …
This tutorials shows how to Round a number up to next half in Excel. If you need to round a number up to the next half, you can use the CEILING function, which always rounds up based on a supplied multiple. Formula =CEILING(number,0.5) Note that MROUND also rounds based on a supplied multiple, but it always rounds to …
This tutorials shows how to Round a number up to nearest multiple in Excel. If you need to round a number up to the nearest specified multiple (i.e. round a number up to the nearest dollar, up to the nearest $.25, up to the nearest multiple of 5, etc.) you can use the CEILING function. Formula =CEILING(number,multiple) …
This tutorials shows how to Round a number up in Excel. If you need to round a number up, regardless of the value of the digits being rounded (i.e. you want to force any number to round up, regardless of value) you can use the ROUNDUP function with a specified number of digits. Formula =ROUNDUP(number,digits) …
This tutorials shows how to Round a number to nearest multiple in Excel. If you need to round a number to the nearest specified multiple (i.e. round a number to the nearest dollar, nearest $.25, nearest multiple of 5 or 10, etc) you can use the MROUND function. Formula =MROUND(number,multiple) Explanation In the example, the …
This tutorials shows how to Round a number to n significant digits in Excel. If you need to round a number to a given (variable) number of specified digits or figures, you can do so with an elegant formula that uses the ROUND and LOG10 functions. Formula =ROUND(number,digits-(1+INT(LOG10(ABS(number))))) Explanation In the example shown, the formula …
This tutorials shows how to Round a number down to nearest multiple in Excel. If you need to round a number down to the nearest specified multiple (i.e. round a number down to the nearest dollar, down to the nearest $.25, down to the nearest multiple of 5, etc.) you can use the FLOOR function. Formula =FLOOR(number,multiple) …
If you need to round to force any number to round down, regardless of its value you can use the ROUNDDOWN function with a specified number of digits. This tutorials shows how to Round a number down in Excel. Formula =ROUNDDOWN(number,digits) Explanation In the example, the formula in cell D7 is =ROUNDDOWN(B7,C7) This tells Excel …
If you need to round a number in a normal fashion (i.e. round values of 5 or more up and values less than 5 down) you can use the ROUND function with a specified number of digits. This tutorials shows how to Round specific number in Excel. Formula =ROUND(number,digits) Explanation In the example, the formula in …
This tutorials shows how to get the number at a specific place value in Excel. To get the number at a specific place value you can use a formula based on the MOD function. By place value, we mean hundred thousands, ten thousands, thousands, hundreds, tens, ones, etc. Formula =MOD(number,place*10) – MOD(number,place) Explanation In the …
This tutorials shows how to Get integer part of a number in Excel. To remove the decimal part of a number and return only the integer portion, you can use the TRUNC function to slice off the decimal. Formula =TRUNC(number) Explanation In the example, cell C6 contains this formula: =TRUNC(B6) The TRUNC function simply truncates …
This tutorials shows how to Get decimal part of a number in Excel. If you need to get just de the decimal part of a number, you can use a simple formula based on the TRUNC function. Formula =number-TRUNC(number) Explanation In the example, cell C6 contains this formula: =B6-TRUNC(B6) This formula uses the TRUNC function …
This tutorial shows how to Count if two criteria match in Excel using the example below; Formula =COUNTIFS(range1,critera1,range2,critera2) Explanation If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a …
This tutorial shows how to Count matches between two columns in Excel using the example below; Formula =SUMPRODUCT(–(range1=range2)) Explanation If you want to compare two columns and count matches in corresponding rows, you can use the SUMPRODUCT function with a simple comparison of the two ranges. For example, if you have values in B5:B11 and …
This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(–((criteria1)+(criteria2)>0)) Explanation To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is: =SUMPRODUCT(–((C5:C11=”blue”)+(D5:D11=”dog”)>0)) How this formula works In the example shown, we …
This tutorial shows how to Count total matches in two ranges in Excel using the example below; Formula =SUMPRODUCT(COUNTIF(range1,range2)) Explanation If you want to compare two ranges, and count total matches between the two ranges, you can use a formula that combines the COUNTIF and SUMPRODUCT functions. Context Suppose you have a “master” list of some …
This tutorial shows how to Count visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(3,range) Explanation If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter. The SUBTOTAL function can perform calculations …
This tutorial shows how to Count rows that contain specific values in Excel using the example below; Formula =SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)))>0)) Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: {=SUM(–(MMULT(–(data=90),TRANSPOSE(COLUMN(data)))>0))} where data is the named range B4:B12. Note: this …
This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden …
This tutorial shows how to work SUMPRODUCT count multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(ISNUMBER(MATCH(range1,{“A”,”B”},0))*ISNUMBER(MATCH(range2,{“X”,”Y”,”Z”},0))) Explanation To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is: =SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{“A”,”B”},0))* ISNUMBER(MATCH(C5:C11,{“X”,”Y”,”Z”},0))) This formula returns a count of rows …
This tutorial shows how to Sum bottom n values in Excel. You can use a combination of SUMPRODUCT function and SMALL function to get the sum bottom n values in the example below; Formula =SUMPRODUCT(SMALL(range,{1,2,n})) Explanation If you need to sum or add the bottom values in a range, you can do so with a formula that uses the …
This tutorial shows how to Sum bottom n values with criteria in Excel. You can use a combination of SUM function, SMALL function and IF function to get the sum bottom in the example below; Formula {=SUM(SMALL(IF(range1=criteria,range2),{1,2,3,N}))} Explanation To sum the bottom n values in a range matching criteria, you can use an array formula based on the …
This tutorial shows how to Sum last n columns in Excel using the example below; Formula =SUM(INDEX(data,0,COLUMNS(data)-(n-1)):INDEX(data,0,COLUMNS(data))) Explanation To sum the last n columns in a table of data (i.e. last 3 columns, last 4 columns, etc.) you can use a formula based on the INDEX function. In the example shown, the formula in K5: …
This tutorial shows how to Sum visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(9,range) Explanation If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL …
This example teaches you how to simulate the roll of two dice in Excel. Note: the instructions below do not teach you how to format the worksheet. We assume that you know how to change font sizes, font styles, insert rows and columns, add borders, change background colors, etc. 1. At the moment, each cell contains …
The ABS function in Excel returns the absolute value of a number. In other words: the ABS function removes the minus sign (-) from a negative number, making it positive. 1. For example, the ABS function in cell B1 below returns the absolute value of a negative number. The ABS function has no effect on 0 (zero) or positive …
To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The test counts twice as much as the quiz and the final exam counts three times as much as the quiz”. 2. Below you can find the …
This example illustrates two functions to chop off decimals in Excel. The INT and the TRUNC function. Int The INT (Integer) function rounds a number down to the nearest integer. Conclusion: the INT function only chops off decimals from positive numbers. Trunc Truncate a number to a given precision. The TRUNC (Truncate) function always chops off decimals. The TRUNC function can keep …
Find Nearest Multiple in Excel This example illustrates three functions to round numbers to a multiple of x in Excel. The MROUND, CEILING and the FLOOR function. Mround 1. For example, round a number to the nearest multiple of 10. 2. For example, round a number to the nearest multiple of 5. Ceiling The same as MROUND but rounds up. Floor The same as …
This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd. Even 1. Rounds a positive number up to the nearest even integer. 2. Rounds a negative number down to the nearest even integer. Odd 1. Rounds a positive number up to the nearest odd integer. …