Excel Formulas Part 1: Your Essential Formula Handbook

Basic Formulas:
These are the building blocks of Excel calculations. With formulas, you can perform various mathematical operations on your data, such as addition, subtraction, multiplication, and division.

Note: In order to calculate or utilize any function in Excel, you must precede it with the equals sign “=” before entering the function. ex: =2+3

Cell Reference — Absolute $:
In Excel, cell references play a crucial role in formulas. Absolute cell references, denoted by the “$” symbol, lock the reference to a specific cell when copied, ensuring consistency. This is particularly useful in dynamic formulas, which automatically update when the underlying data changes.

In this scenario, our goal is to calculate percentages based on a fixed reference point, A13. To achieve this, we could manually input formulas like A2/A13 for each calculation. However, this method would be time-consuming. By keeping cell A13 constant using absolute referencing (denoted as $A$13), we can quickly drag the formula across other cells. This ensures that the denominator (A13) remains unchanged, while the numerator adjusts accordingly, streamlining the process and saving time.

Function Name (Arguments):
Excel functions are predefined formulas that perform specific tasks. They typically consist of a function name followed by arguments enclosed in parentheses. Arguments are the inputs that the function operates on.

If u want to see all the functions available in Excel follow below steps:

In Ribbon tab click on -> formulas -> fx Insert Function

Function name: SUM, Arguments: A2:A11

=SUM(A2:A11)

— SUM():Adds up the values in a range of cells.
— AVERAGE(): Calculates the average of values in a range.

— MID, UPPER, PROPER, RIGHT, LEFT:Text manipulation functions for extracting or modifying parts of text strings.
— CONCAT: Combines multiple text strings into one.
— TRIM: Removes extra spaces from text.

Date and Time:
Excel represents dates and times internally as serial numbers, with January 1, 1900, serving as the starting date (serial number 1). You can perform various date and time calculations using Excel functions.

Examples of Date and Time Functions:
— TODAY(), NOW(): Returns the current date and time.
— MONTH(), DAY(), YEAR():Extracts the month, day, and year from a given date.
— DATE(): Creates a date from specified year, month, and day values.
— NETWORKDAYS(), NETWORKDAYS.INTL(): Calculates the number of working days between two dates, excluding weekends and optional holidays.

Errors:
Excel displays error messages when formulas encounter issues, such as dividing by zero or referencing empty cells. Understanding and troubleshooting errors is essential for maintaining accurate calculations.

IF Function, IFs, AND, OR:
— IF(): Evaluates a condition and returns one value if the condition is true and another if false.
— IFS(): Evaluates multiple conditions and returns a value corresponding to the first true condition.

— AND, OR: Logical functions that allow you to test multiple conditions simultaneously.

SUMIF, AVERAGEIF, COUNTIF:
These functions perform calculations based on specified criteria:
— SUMIF(): Adds up values in a range that meet a given condition.
— AVERAGEIF(): Calculates the average of values in a range that meet a given condition.
— COUNTIF(): Counts the number of cells in a range that meet a given condition.

Formulas:

MID(text,start_num,num_chars)
RIGHT(text,num_chars)
LEFT(text,num_chars)
PROPER(text)
UPPER(text)
LOWER(text)
TRIM(text)
CONCAT(text1,...)

TODAY()
NOW()
MONTH(serial_number)
DAY(serial_number)
YEAR(serial_number)
DATE(year,month,day)
NETWORKDAYS(start_date,end_date,holidays)
NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)
DATEDIF(start_date,end_date,unit) unit - year( Y), M, D,MD, YM

IF(logical_test,value_if_true,value_if_false)
IFS(logical_test,value_if_true,...)
AND(logical1,logical2,...)
OR(logical1,logical2,...)

SUMIF(range,criteria,sum_range)
AVERAGEIF(range,criteria,average_range)
COUNTIF(range,criteria)