Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Managing Conditional Formatting Rules in Excel
  • Reverse List in Excel
  • Understanding Anova in Excel

References

  • How to get address of last cell in range in Excel
  • To count total rows in a range in Excel
  • How to create dynamic named range with OFFSET in Excel
  • Merge tables with VLOOKUP in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation must contain specific text
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only
  • Prevent invalid data entering in specific cells

Category: Math and Trig Functions

Excel Math & Trig functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios.

SUMIFS function: Description, Usage, Syntax, Examples and Explanation

by

What is SUMIFS function in Excel? SUMIFS function is one of the Math and Trig functions in Microsoft Excel that adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose …

Continue Reading

ATANH function: Description, Usage, Syntax, Examples and Explanation

by

What is ATANH function in Excel? ATANH function is one of the Math and Trig functions in Microsoft Excel that returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number. Syntax of …

Continue Reading

FLOOR.MATH function: Description, Usage, Syntax, Examples and Explanation

by

What is FLOOR.MATH function in Excel? FLOOR.MATH function is one of the Math and Trig functions in Microsoft Excel that round a number down to the nearest integer or to the nearest multiple of significance. Syntax of FLOOR.MATH function FLOOR.MATH(number, significance, mode) The FLOOR.MATH function syntax has the following arguments. Number: The number to be rounded …

Continue Reading

Round numbers in Excel

by

This chapter illustrates three Mathematical functions ROUND, ROUNDUP and ROUNDDOWN. Navigation: Formula Tab → Function Library Group → Math & Trig Category Before your start: if you round a number, you lose precision. If you don’t want this, show fewer decimal places without changing the number itself. Round Syntax: ROUND(number, num_digits) 1. Round a number to two decimal places. …

Continue Reading

MINVERSE function: Description, Usage, Syntax, Examples and Explanation

by

Examples of MINVERSE function Example 1 Steps to follow: 1. Open a new Excel worksheet. 2. Copy data in the following table below and paste it in cell A1 Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter. You can adjust the column widths to see all …

Continue Reading

SECH function: Description, Usage, Syntax, Examples and Explanation

by

What is SECH function in Excel? SECH function is one of the Math and Trig functions in Microsoft Excel that returns the hyperbolic secant of an angle. Syntax of SECH function SECH(number) The SECH function syntax has the following arguments. Number: Number is the angle in radians for which you want the hyperbolic secant. SECH formula …

Continue Reading

SUMIF function: Description, Usage, Syntax, Examples and Explanation

by

What is SUMIF function in Excel? SUMIF function is one of the Math and Trig functions in Microsoft Excel that is used to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger …

Continue Reading

BASE function: Description, Usage, Syntax, Examples and Explanation

by

What is BASE function in Excel? BASE function is one of the Math and Trig functions in Microsoft Excel that r converts a number into a text representation with the given radix (base). Syntax of BASE function BASE(Number, Radix [Min_length]) The BASE function syntax has the following arguments. Number: The number that you want to convert. …

Continue Reading

FLOOR function: Description, Usage, Syntax, Examples and Explanation

by

What is FLOOR function in Excel? FLOOR function is one of the Math and Trig functions in Microsoft Excel that rounds number down, toward zero, to the nearest multiple of significance. Syntax of FLOOR function FLOOR(number, significance) The FLOOR function syntax has the following arguments: Number: The numeric value you want to round. Significance: The multiple …

Continue Reading

MUNIT function: Description, Usage, Syntax, Examples and Explanation

by

What is MUNIT function in Excel? MUNIT function is one of the Math and Trig functions in Microsoft Excel that returns the unit matrix for the specified dimension. Syntax of MUNIT function MUNIT(dimension) The MUNIT function syntax has the following arguments. Dimension: Dimension is an integer specifying the dimension of the unit matrix that you want …

Continue Reading

SERIESSUM function: Description, Usage, Syntax, Examples and Explanation

by

What is SERIESSUM function in Excel? SERIESSUM function is one of the Math and Trig functions in Microsoft Excel that returns the sum of a power series based on the formula: Many functions can be approximated by a power series expansion. Syntax of SERIESSUM function SERIESSUM(x, n, m, coefficients) The SERIESSUM function syntax has the following …

Continue Reading

SUBTOTAL function: Description, Usage, Syntax, Examples and Explanation

by

What is SUBTOTAL function in Excel? SUBTOTAL function is one of the Math and Trig functions in Microsoft Excel that returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is …

Continue Reading

CEILING function: Description, Usage, Syntax, Examples and Explanation

by

What is CEILING function in Excel? CEILING function is one of the Math and Trig functions in Microsoft Excel that returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula …

Continue Reading

FACTDOUBLE function: Description, Usage, Syntax, Examples and Explanation

by

What is FACTDOUBLE function in Excel? FACTDOUBLE function is one of the Math and Trig functions in Microsoft Excel that returns the double factorial of a number. Syntax of FACTDOUBLE function FACTDOUBLE(number) The FACTDOUBLE function syntax has the following arguments: Number: The value for which to return the double factorial. If number is not an integer, …

Continue Reading

MULTINOMIAL function: Description, Usage, Syntax, Examples and Explanation

by

What is MULTINOMIAL function in Excel? MULTINOMIAL function is one of the Math and Trig functions in Microsoft Excel that returns the ratio of the factorial of a sum of values to the product of factorials. Syntax of MULTINOMIAL function MULTINOMIAL(number1, [number2], …) The MULTINOMIAL function syntax has the following arguments: Number1, number2, …    Number1 is required, …

Continue Reading

SEQUENCE function: Description, Usage, Syntax, Examples and Explanation

by

What is SEQUENCE function in Excel? SEQUENCE function is one of the Math and Trig functions in Microsoft Excel that allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. Syntax of SEQUENCE function =SEQUENCE(rows, [columns], [start], [step]) SEQUENCE(number of row, number of columns,starting number, step size) …

Continue Reading

TRUNC function: Description, Usage, Syntax, Examples and Explanation

by

What is TRUNC function in Excel? TRUNC function is one of the Math and Trig functions in Microsoft Excel that truncates a number to an integer by removing the fractional part of the number. Syntax of TRUNC function TRUNC(number, [num_digits]) The TRUNC function syntax has the following arguments: Number: The number you want to truncate. Num_digits(Optional): …

Continue Reading

CEILING.MATH function: Description, Usage, Syntax, Examples and Explanation

by

What is CEILING.MATH function in Excel? CEILING.MATH function is one of the Math and Trig functions in Microsoft Excel that rounds a number up to the nearest integer or to the nearest multiple of significance. Syntax of CEILING.MATH function CEILING.MATH(number, [significance], [mode]) The CEILING.MATH function syntax has the following arguments. Number: Number must be less than …

Continue Reading

FACT function: Description, Usage, Syntax, Examples and Explanation

by

What is FACT function in Excel? FACT function is one of the Math and Trig functions in Microsoft Excel that returns the factorial of a number. The factorial of a number is equal to 1*2*3*…* number. Syntax of FACT function FACT(number) The FACT function syntax has the following arguments: Number: The nonnegative number for which you …

Continue Reading

SUMX2PY2 function: Description, Usage, Syntax, Examples and Explanation

by

What is SUMX2PY2 function in Excel? SUMX2PY2 function is one of the Math and Trig functions in Microsoft Excel that returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations. Syntax of SUMX2PY2 function SUMX2PY2(array_x, array_y) The …

Continue Reading

SUM function: Description, Usage, Syntax, Examples and Explanation

by

What is SUM function in Excel? SUM function is one of Math and Trig functions in Microsoft Office Excel that adds all numbers in a range of cells and returns the result. Syntax of SUM function The syntax for the SUM function in Microsoft Excel is: SUM( number1, [number2, … number_n] ) OR SUM ( cell1:cell2, [cell3:cell4], …

Continue Reading

How to return TRUE when all cells in range are blank in Excel

by

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 …

Continue Reading

Change negative numbers to positive in Excel

by

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 …

Continue Reading

Cash denomination calculator in Excel

by

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 …

Continue Reading

Calculate a ratio from two numbers in Excel

by

To generate the ratio of two numbers to each other (e.g. 4:3, 16:9, etc.), you can do using division, the GCD function, and concatenation. In the formula  of the formula below num1 represents the first number (the antecedent) and num2 represents the second number (the consequent). Formula =num1/GCD(num1,num2)&”:”&num2/GCD(num1,num2) Explanation In the example, the active cell contains this …

Continue Reading

Convert feet and inches to inches in Excel

by

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 …

Continue Reading

Convert inches to feet and inches in Excel

by

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 …

Continue Reading

How to check cell equals one of many things in Excel

by

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 …

Continue Reading

Round to nearest 1000 in Excel

by

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 …

Continue Reading

Round to nearest 5 in Excel

by

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 …

Continue Reading

Round a number down to nearest multiple in Excel

by

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) …

Continue Reading

Excel Get decimal part of a number Example

by

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 …

Continue Reading

Excel Get integer part of a number Example

by

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 …

Continue Reading

Excel Get number at place value Example

by

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 …

Continue Reading

Excel Round a number Example

by

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 …

Continue Reading

Excel Round a number down Example

by

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 …

Continue Reading

Round a number to n significant digits in Excel

by

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 …

Continue Reading

Round a number to nearest multiple in Excel

by

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 …

Continue Reading

Round a number up in Excel

by

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) …

Continue Reading

Round a number up to nearest multiple in Excel

by

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) …

Continue Reading

Posts navigation

  • Previous
  • 1
  • 2
  • 3
  • 4
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function example in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal minutes
  • How to calculate nth day of year in Excel
  • Check If Two Dates are same month in Excel
  • How to calculate nth day of week in month in Excel

Grouping

  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Count cells that do not contain many strings in Excel
  • How to calculate percentage discount in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • How to choose page/paper size in Excel before Printing
  • How to generate random number between two numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning