Excel OfficeExcel Math & Trig functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios.
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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… read more »
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)… read more »
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)… read more »