Abdul Alim: a little bit learning, let's go

Excel, VBA, Power BI and Digital Marketing tutorials  

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. 

Lookup/Ref Functions 

ADDRESS : Returns a cell reference as a text string. Learn more..... 

AREAS : Returns the number of areas in a reference. Learn more..... 

CHOOSE : Selects a value from a list using an index number. Learn more..... 

COLUMN : Returns the column number of a cell reference. Learn more..... 

COLUMNS : Returns the number of columns in an array or reference. Learn more..... 

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. Learn more..... 

HYPERLINK : Creates a shortcut to a document, picture, webpage, or program. Learn more..... 

INDEX : Returns a value from a table based on an index. Learn more..... 

INDIRECT : Returns the value of a cell referenced by a text string. Learn more..... 

LOOKUP : Looks up values in a vector or array. Learn more..... 

MATCH : Searches for a value in a range and returns the relative position. Learn more..... 

OFFSET : Returns a reference that is a specified number of rows and columns from a given reference. Learn more..... 

ROW : Returns the row number of a cell reference. Learn more..... 

ROWS : Returns the number of rows in a reference or array. Learn more..... 

TRANSPOSE : Returns the transpose of an array. Learn more..... 

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. Learn more..... 

XLOOKUP : Searches for a value in a range and returns a corresponding value in a second range. Learn more..... 

String/Text Functions 

BAHTTEXT : Converts a number to text, using currency format. Learn more..... 

CHAR : Returns the character specified by the code number. Learn more..... 

CLEAN : Removes all nonprintable characters from text. Learn more..... 

CODE : Returns the numeric code for the first character in a text string. Learn more..... 

CONCAT : Joins two or more text strings into one. Learn more..... 

CONCATENATE : Joins two or more text strings into one text string. Learn more..... 

DOLLAR : Converts a number to text, using currency format. Learn more..... 

EXACT : Checks to see if two text strings are identical. Learn more..... 

FIND : Finds one text value within another (case-sensitive). Learn more..... 

FIXED : Formats a number as text with a fixed number of decimals. Learn more..... 

LEFT : Returns the leftmost characters from a text value. Learn more..... 

LEN : Returns the number of characters in a text string. Learn more..... 

LOWER : Converts text to lowercase. Learn more..... 

MID : Returns a specific number of characters from a text string starting at the position you specify. Learn more..... 

NUMBERVALUE : Converts a text string that represents a number to a number. Learn more..... 

PROPER : Capitalizes the first letter in each word of a text value. Learn more..... 

REPLACE : Replaces characters within text. Learn more..... 

REPT : Repeats text a given number of times. Learn more..... 

RIGHT : Returns the rightmost characters from a text value. Learn more..... 

SEARCH : Finds one text value within another (not case-sensitive). Learn more..... 

SUBSTITUTE : Substitutes new text for old text in a text string. Learn more..... 

T : Converts its arguments to text. Learn more..... 

TEXT : Formats a number and converts it to text. Learn more..... 

TEXTJOIN : Combines text from multiple ranges or strings, and includes a delimiter you specify between each text value. Learn more..... 

TRIM : Removes spaces from text. Learn more..... 

UNICHAR : Returns the Unicode character that is references by the numeric value. Learn more..... 

UNICODE : Returns the Unicode value of the first character in the text argument. Learn more..... 

UPPER : Converts text to uppercase. Learn more..... 

VALUE : Converts a text string that represents a number to a numeric value. Learn more..... 

Date/Time Functions 

DATE : Returns the serial number of a particular date. Learn more..... 

DATEDIF : Calculates the number of days, months, or years between two dates. Learn more..... 

DATEVALUE : Converts a date in the form of text to a serial number. Learn more..... 

DAY : Converts a serial number to a day of the month. Learn more..... 

DAYS : Calculates the number of days between two dates. Learn more..... 

DAYS360 : Calculates the number of days between two dates based on a 360-day year. Learn more..... 

EDATE : Returns the serial number of the date that is a specified number of months before or after the start date. Learn more..... 

EOMONTH : Returns the serial number of the last day of the month before or after a specified number of months. Learn more..... 

HOUR : Converts a serial number to an hour. Learn more..... 

MINUTE : Converts a serial number to a minute. Learn more..... 

MONTH : Converts a serial number to a month. Learn more..... 

NETWORKDAYS : Returns the number of whole working days between start_date and end_date. Learn more..... 

NETWORKDAYS.INTL : Returns the number of whole workdays between two dates with custom weekend parameters. Learn more..... 

NOW : Returns the serial number of the current date and time. Learn more..... 

SECOND : Converts a serial number to a second. Learn more..... 

TIME : Returns the serial number of a particular time. Learn more..... 

TIMEVALUE : Converts a time in the form of text to a serial number. Learn more..... 

TODAY : Returns the serial number of today’s date. Learn more..... 

WEEKDAY : Converts a serial number to a day of the week. Learn more..... 

WEEKNUM : Converts a serial number to a number representing where the week falls numerically within a year. Learn more..... 

WORKDAY : Returns the serial number of the date before or after a specified number of workdays. Learn more..... 

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. Learn more..... 

YEAR : Converts a serial number to a year. Learn more..... 

YEARFRAC : Returns the year fraction representing the number of whole days between start_date and end_date. Learn more..... 

Math/Trig Functions 

SUMIF : Adds the cells specified by a given criteria. Learn more..... 

SUMIFS : Adds the cells in a range that meet multiple criteria. Learn more..... 

SUMPRODUCT : Returns the sum of the products of corresponding ranges or arrays. Learn more..... 

Statistical Functions 

AVERAGEIF : Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. Learn more..... 

COUNTIF : Counts the number of cells within a range that meet the given condition. Learn more..... 

COUNTIFS : Counts the number of cells within a range that meet multiple conditions. Learn more..... 

Logical Functions 

AND : Returns TRUE if all of its arguments are TRUE. Learn more..... 

FALSE : Returns the logical value FALSE. Learn more..... 

IF : Specifies a logical test to perform. Learn more..... 

IFERROR : Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Learn more..... 

IFNA : Returns the value you specify if the formula returns the #N/A error value; otherwise, returns the result of the formula. Learn more..... 

IFS : Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. Learn more..... 

NOT : Reverses the logic of its argument. Learn more..... 

OR : Returns TRUE if any argument is TRUE. Learn more..... 

SWITCH : Evaluates an expression against a list of values and returns the result corresponding to the first matching value. Learn more..... 

TRUE : Returns the logical value TRUE. Learn more..... 

Information Functions 

CELL : Returns information about the formatting, location, or contents of a cell. Learn more..... 

ERROR.TYPE : Returns a number corresponding to an error type. Learn more..... 

INFO : Returns information about the current operating environment. Learn more..... 

ISBLANK : Returns TRUE if the value is blank. Learn more..... 

ISERR : Returns TRUE if the value is any error value except #N/A. Learn more..... 

ISERROR : Returns TRUE if the value is any error value. Learn more..... 

ISLOGICAL : Returns TRUE if the value is a logical value. Learn more..... 

ISNA : Returns TRUE if the value is the #N/A error value. Learn more..... 

ISNONTEXT : Returns TRUE if the value is not text. Learn more..... 

ISNUMBER : Returns TRUE if the value is a number. Learn more..... 

ISREF : Returns TRUE if the value is a reference. Learn more..... 

ISTEXT : Returns TRUE if the value is text. Learn more..... 

N : Returns a value converted to a number. Learn more..... 

NA : Returns the error value #N/A. Learn more..... 

TYPE : Returns a number indicating the data type of a value. Learn more..... 

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.