# Formulas

## Introduction

First off, let’s take a look at Excel, a formidable tool for data analysis. It hosts an impressive array of advanced formulas that can dramatically streamline your interactions with data. You’ll find that these formulas, encompassing everything from lookup functions to text, date/time, mathematical, statistical, logical, and information functions, morph Excel into a versatile spreadsheet calculator, perfectly suited to various types of data analysis tasks. Consequently, our goal here is to dive deep into these formulas, revealing their purpose, their usage, and how to manage common errors. We’ll also touch on best practices, functions that are often paired together, and some of the most frequently asked questions. With this guide in hand, even those new to Excel can begin crafting custom formulas and deploying advanced Excel functions for their data analysis needs.

Breaking Down the Formulas

Understanding what each formula does and when to use it is crucial for working efficiently with Excel. So, we’ll be dedicating a section to dissect each category of formulas and provide examples for clarity. In no time, you’ll find that you’ve grasped the essence of each function and can manipulate them to serve your specific needs.

 Category Formula Name Description Lookup/Ref Functions ADDRESS Returns a cell reference as a text string Lookup/Ref Functions AREAS Returns the number of areas in a reference Lookup/Ref Functions CHOOSE Selects a value from a list using an index number Lookup/Ref Functions COLUMN Returns the column number of a cell reference Lookup/Ref Functions COLUMNS Returns the number of columns in an array or reference Lookup/Ref Functions HLOOKUP Searches for a value in the top row of a table and returns a value in the same column from a row you specify Lookup/Ref Functions HYPERLINK Creates a shortcut to a document, picture, webpage, or program Lookup/Ref Functions INDEX Returns a value from a table based on an index Lookup/Ref Functions INDIRECT Returns the value of a cell referenced by a text string Lookup/Ref Functions LOOKUP Looks up values in a vector or array Lookup/Ref Functions MATCH Searches for a value in a range and returns the relative position Lookup/Ref Functions OFFSET Returns a reference that is a specified number of rows and columns from a given reference Lookup/Ref Functions ROW Returns the row number of a cell reference Lookup/Ref Functions ROWS Returns the number of rows in a reference or array Lookup/Ref Functions TRANSPOSE Returns the transpose of an array Lookup/Ref Functions VLOOKUP Searches for a value in the first column of a table and returns a value in the same row from a column you specify Lookup/Ref Functions XLOOKUP Searches for a value in a range and returns a corresponding value in a second range String/Text Functions BAHTTEXT Converts a number to text, using currency format String/Text Functions CHAR Returns the character specified by the code number String/Text Functions CLEAN Removes all nonprintable characters from text String/Text Functions CODE Returns the numeric code for the first character in a text string String/Text Functions CONCAT Joins two or more text strings into one String/Text Functions CONCATENATE Joins two or more text strings into one text string String/Text Functions DOLLAR Converts a number to text, using currency format String/Text Functions EXACT Checks to see if two text strings are identical String/Text Functions FIND Finds one text value within another (case-sensitive) String/Text Functions FIXED Formats a number as text with a fixed number of decimals String/Text Functions LEFT Returns the leftmost characters from a text value String/Text Functions LEN Returns the number of characters in a text string String/Text Functions LOWER Converts text to lowercase String/Text Functions MID Returns a specific number of characters from a text string starting at the position you specify String/Text Functions NUMBERVALUE Converts a text string that represents a number to a number String/Text Functions PROPER Capitalizes the first letter in each word of a text value String/Text Functions REPLACE Replaces characters within text String/Text Functions REPT Repeats text a given number of times String/Text Functions RIGHT Returns the rightmost characters from a text value String/Text Functions SEARCH Finds one text value within another (not case-sensitive) String/Text Functions SUBSTITUTE Substitutes new text for old text in a text string String/Text Functions T Converts its arguments to text String/Text Functions TEXT Formats a number and converts it to text String/Text Functions TEXTJOIN Combines text from multiple ranges or strings, and includes a delimiter you specify between each text value String/Text Functions TRIM Removes spaces from text String/Text Functions UNICHAR Returns the Unicode character that is references by the numeric value String/Text Functions UNICODE Returns the Unicode value of the first character in the text argument String/Text Functions UPPER Converts text to uppercase String/Text Functions VALUE Converts a text string that represents a number to a numeric value Date/Time Functions DATE Returns the serial number of a particular date Date/Time Functions DATEDIF Calculates the number of days, months, or years between two dates Date/Time Functions DATEVALUE Converts a date in the form of text to a serial number Date/Time Functions DAY Converts a serial number to a day of the month Date/Time Functions DAYS Calculates the number of days between two dates Date/Time Functions DAYS360 Calculates the number of days between two dates based on a 360-day year Date/Time Functions EDATE Returns the serial number of the date that is a specified number of months before or after the start date Date/Time Functions EOMONTH Returns the serial number of the last day of the month before or after a specified number of months Date/Time Functions HOUR Converts a serial number to an hour Date/Time Functions MINUTE Converts a serial number to a minute Date/Time Functions MONTH Converts a serial number to a month Date/Time Functions NETWORKDAYS Returns the number of whole working days between start_date and end_date Date/Time Functions NETWORKDAYS.INTL Returns the number of whole workdays between two dates with custom weekend parameters Date/Time Functions NOW Returns the serial number of the current date and time Date/Time Functions SECOND Converts a serial number to a second Date/Time Functions TIME Returns the serial number of a particular time Date/Time Functions TIMEVALUE Converts a time in the form of text to a serial number Date/Time Functions TODAY Returns the serial number of today’s date Date/Time Functions WEEKDAY Converts a serial number to a day of the week Date/Time Functions WEEKNUM Converts a serial number to a number representing where the week falls numerically within a year Date/Time Functions WORKDAY Returns the serial number of the date before or after a specified number of workdays Date/Time Functions WORKDAY.INTL Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days Date/Time Functions YEAR Converts a serial number to a year Date/Time Functions YEARFRAC Returns the year fraction representing the number of whole days between start_date and end_date Math/Trig Functions SUMIF Adds the cells specified by a given criteria Math/Trig Functions SUMIFS Adds the cells in a range that meet multiple criteria Math/Trig Functions SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays Statistical Functions AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria Statistical Functions COUNTIF Counts the number of cells within a range that meet the given condition Statistical Functions COUNTIFS Counts the number of cells within a range that meet multiple conditions Logical Functions AND Returns TRUE if all of its arguments are TRUE Logical Functions FALSE Returns the logical value FALSE Logical Functions IF Specifies a logical test to perform Logical Functions IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula Logical Functions IFNA Returns the value you specify if the formula returns the #N/A error value; otherwise, returns the result of the formula Logical Functions IFS Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition Logical Functions NOT Reverses the logic of its argument Logical Functions OR Returns TRUE if any argument is TRUE Logical Functions SWITCH Evaluates an expression against a list of values and returns the result corresponding to the first matching value Logical Functions TRUE Returns the logical value TRUE Information Functions CELL Returns information about the formatting, location, or contents of a cell Information Functions ERROR.TYPE Returns a number corresponding to an error type Information Functions INFO Returns information about the current operating environment Information Functions ISBLANK Returns TRUE if the value is blank Information Functions ISERR Returns TRUE if the value is any error value except #N/A Information Functions ISERROR Returns TRUE if the value is any error value Information Functions ISLOGICAL Returns TRUE if the value is a logical value Information Functions ISNA Returns TRUE if the value is the #N/A error value Information Functions ISNONTEXT Returns TRUE if the value is not text Information Functions ISNUMBER Returns TRUE if the value is a number Information Functions ISREF Returns TRUE if the value is a reference Information Functions ISTEXT Returns TRUE if the value is text Information Functions N Returns a value converted to a number Information Functions NA Returns the error value #N/A Information Functions TYPE Returns a number indicating the data type of a value

### Common Mistakes and Their Fixes

Even seasoned Excel users can run into errors when working with these advanced formulas. Therefore, we’ll dedicate a section to outline some of the most common issues you might encounter and how to troubleshoot them effectively. This way, you can spend less time scratching your head and more time analyzing your data.

## Conclusion

In wrapping up, getting a handle on Excel formulas can massively boost your productivity and your analytical prowess, making you an invaluable player in any field that leans heavily on data. We’ve taken you through various Excel formulas, starting from the ADDRESS function found under Lookup/Ref functions, right through to the TYPE function that’s part of the Information functions category. The key to transforming raw data into valuable insights lies in understanding and using these formulas effectively. So, keep experimenting with these formulas, and continue to explore the vast array of functions that Excel offers. In doing so, you’ll transform your spreadsheet into a powerful data analysis tool. Remember, exploration leads to discovery, and the more you discover, the better you’ll become at Excel.

### Continued Learning

Don’t stop here, though! Excel is constantly evolving and adding new features and formulas. Keep an eye on updates and changes, and make a habit of learning something new about Excel every day. Your future self will thank you.