# Functions of Different Categories in Excel

To analyze your company's payroll expenditures, you might create an Excel spreadsheet and use some of the functions in the Financial or Math & Trigonometry categories. To create a pricing spreadsheet, you might use functions in the Database or Lookup & Reference categories. Because there are hundreds of functions to choose from, Excel groups functions into categories to make it easier to find the right function for a particular calculation. A category, however, is simply for convenience and does not determine how or when functions of different categories in Excel can be used together.

## Categories

Excel groups functions into 12 categories: Compatibility, Cube, Database, Date and Time, Engineering, Financial, Information, Logical, Lookup & Reference, Math & Trigonometry, Statistical and Text. There is an additional category for user-defined functions installed with add-ins.

## Data Types

Functions in all categories share a common set of data types such as number, text, date and Boolean. Each function accepts one or more parameters of a given type and returns a value of a given type. For example, the SUM function accepts two numbers and returns a number. Functions of different categories in Excel often return the same data type. For example, the DATEVALUE function in the Date & Time category also returns a numeric value, which is the serial representation of a character string.

## Compound Expressions

You can create a compound expression using functions in different categories as long as the data types match. Match the data type of the output from one function to the input data type required by another function when you use them together. For example, combine functions from Math & Trigonometry with a function from the Logical category to avoid a possible "divide-by-zero" error in a calculation, as in the example below. The formula uses the IF function to test if the divisor is greater than zero before it performs the division; otherwise it returns zero. Both the logical test and the division use the SUM formula from the Math & Trigonometry category.

=IF(SUM(C1:C4)>0, SUM(A1:A4)/SUM(C1:C4), 0)

## Multiple Solutions

There is often more than one way to arrive at a calculated result using functions of different categories in Excel. For example, you can find the sign of a number using the SIGN function in the Math & Trigonometry category, as in:

=SIGN(A1).

You can calculate the same result with the IF function in the Logical category, using:

=IF(A1>0,"+1", IF(A1<0,"-1",0)).

References

Writer Bio

Steve McDonnell's experience running businesses and launching companies complements his technical expertise in information, technology and human resources. He earned a degree in computer science from Dartmouth College, served on the WorldatWork editorial board, blogged for the Spotfire Business Intelligence blog and has published books and book chapters for International Human Resource Information Management and Westlaw.