First Formula: SUM

The SUM formula is one of the simplest and most commonly used formulas in Excel. It is used to calculate the total (sum) of a range of numbers.

How to Apply SUM:

1. Purpose: To add up the numbers in the "Salary" column.

2. Formula: =SUM(E2:E4)

   • E2:E4 refers to the range of cells in the "Salary" column (50000, 65000, 80000).

Step-by-Step Application:

1. Click on an empty cell (e.g., E5) below the "Salary" column.

2. Type the formula: =SUM(E2:E4).

3. Press Enter.

4. Excel will calculate the total of 50000 + 65000 + 80000 = 195000

Second Formula: AVERAGE

The AVERAGE formula calculates the mean (average) of a range of numbers.

How to Apply AVERAGE:

1. Purpose: To find the average salary in the "Salary" column.

2. Formula: =AVERAGE(E2:E4)

   • E2:E4 refers to the range of cells in the "Salary" column (50000, 65000, 80000).

Step-by-Step Application:

1. Click on an empty cell (e.g., E6) below the "Salary" column.

2. Type the formula: =AVERAGE(E2:E4).

3. Press Enter.

4. Excel will calculate the average: (50000 + 65000 + 80000) ÷ 3 = 65000.

Third Formula: IF

The IF formula is a logical function that checks a condition and returns different values based on whether the condition is TRUE or FALSE.

How to Apply IF:

1. Purpose: To check if the "Performance Score" is above 80% and return "Excellent" if true, otherwise "Needs Improvement."

2. Formula: =IF(G2>80%, "Excellent", "Needs Improvement")

   • G2 refers to the "Performance Score" of the first employee.

   • >80% checks if the score is greater than 80%.

   • "Excellent" is the value returned if the condition is TRUE.

   • "Needs Improvement" is the value returned if the condition is FALSE.

Step-by-Step Application:

1. Go to the column next to "Performance Score" (e.g., H2).

2. Type the formula: =IF(G2>80%, "Excellent", "Needs Improvement").

3. Press Enter.

4. Drag the formula down to apply it to other rows (H3, H4, etc.).

5. The result will display:

   • Row 2: "Excellent" (92% > 80%).

   • Row 3: "Needs Improvement" (76% < 80%).

Fourth Formula: VLOOKUP

The VLOOKUP formula is used to search for a value in the first column of a table and return a value in the same row from another column.

How to Apply VLOOKUP:

1. Purpose: To find the "Department" of an employee using their "ID."

2. Formula: =VLOOKUP(1, A2:G4, 6, FALSE)

   • 1: The value to look up (ID = 1).

   • A2:G4: The table range to search within.

   • 6: The column number to return the value from (Department column is the 6th column in the range).

   • FALSE: Ensures an exact match.

Step-by-Step Application:

1. In an empty cell (e.g., I1), type: Employee Department.

2. In the cell below it (e.g., I2), type the formula: =VLOOKUP(1, A2:G4, 6, FALSE).

3. Press Enter.

4. Excel will return Sales because ID 1 corresponds to the "Sales" department.

Fifth Formula: CONCATENATE or CONCAT

The CONCATENATE (or CONCAT in newer versions of Excel) formula is used to join two or more pieces of text into one.

How to Apply CONCATENATE/CONCAT:

1. Purpose: To combine the "Name" and "Department" into a single string, such as "John Doe - Sales."

2. Formula: =CONCATENATE(B2, " - ", F2)

   • B2: Refers to the "Name" column.

   • " - ": Adds a separator between the name and department.

   • F2: Refers to the "Department" column.

Step-by-Step Application:

1. In a new column (e.g., J1), type: Name and Department.

2. In the cell below it (e.g., J2), type the formula: =CONCATENATE(B2, " - ", F2).

3. Press Enter.

4. Drag the formula down to apply it to other rows (J3, J4, etc.).

5. The results will be:

   • Row 2: John Doe - Sales

   • Row 3: Jane Smith - HR

   • Row 4: Alan Brown - IT

Sixth Formula: COUNTIF

The COUNTIF formula counts the number of cells in a range that meet a specific condition.

How to Apply COUNTIF:

1. Purpose: To count the number of employees with a "Performance Score" greater than 80%.

2. Formula: =COUNTIF(G2:G4, ">80%")

   • G2:G4: The range of cells in the "Performance Score" column.

   • ">80%": The condition to count only scores greater than 80%.

Step-by-Step Application:

1. In an empty cell (e.g., K1), type: High Performers Count.

2. In the cell below it (e.g., K2), type the formula: =COUNTIF(G2:G4, ">80%").

3. Press Enter.

4. Excel will return 2 because two employees (92% and 85%) have performance scores greater than 80%.

How to Apply LEFT:

1. Purpose: To extract the first three letters of each employee's name in the "Name" column.

2. Formula: =LEFT(B2, 3)

   • B2: Refers to the "Name" column.

   • 3: Indicates the number of characters to extract from the left.

Step-by-Step Application:

1. In a new column (e.g., L1), type: Name Initials.

2. In the cell below it (e.g., L2), type the formula: =LEFT(B2, 3).

3. Press Enter.

4. Drag the formula down to apply it to other rows (L3, L4, etc.).

5. The results will be:

   • Row 2: Joh (from "John Doe").

   • Row 3: Jan (from "Jane Smith").

   • Row 4: Ala (from "Alan Brown").

Eighth Formula: LEN

The LEN formula calculates the total number of characters in a text string, including spaces.

How to Apply LEN:

1. Purpose: To count the number of characters in each employee's name in the "Name" column.

2. Formula: =LEN(B2)

   • B2: Refers to the "Name" column.

Step-by-Step Application:

1. In a new column (e.g., M1), type: Name Length.

2. In the cell below it (e.g., M2), type the formula: =LEN(B2).

3. Press Enter.

4. Drag the formula down to apply it to other rows (M3, M4, etc.).

5. The results will be:

   • Row 2: 8 (John Doe has 8 characters including the space).

   • Row 3: 10 (Jane Smith has 10 characters including the space).

   • Row 4: 10 (Alan Brown has 10 characters including the space).

Ninth Formula: TRIM

The TRIM formula removes all extra spaces from a text string, leaving only single spaces between words.

How to Apply TRIM:

1. Purpose: To clean up text by removing unnecessary spaces from names in the "Name" column.

2. Formula: =TRIM(B2)

   • B2: Refers to the "Name" column.

Step-by-Step Application:

1. In a new column (e.g., N1), type: Cleaned Name.

2. In the cell below it (e.g., N2), type the formula: =TRIM(B2).

3. Press Enter.

4. Drag the formula down to apply it to other rows (N3, N4, etc.).

5. The results will show the cleaned names:

   • For example, if there were extra spaces around "John Doe", the TRIM formula would return "John Doe" without extra spaces.

Tenth Formula: PROPER

The PROPER formula capitalizes the first letter of each word in a text string while converting the remaining letters to lowercase.

How to Apply PROPER:

1. Purpose: To format names in the "Name" column so that each word starts with a capital letter.

2. Formula: =PROPER(B2)

   • B2: Refers to the "Name" column.

Step-by-Step Application:

1. In a new column (e.g., O1), type: Proper Case Name.

2. In the cell below it (e.g., O2), type the formula: =PROPER(B2).

3. Press Enter.

4. Drag the formula down to apply it to other rows (O3, O4, etc.).

5. The results will show:

   • Row 2: John Doe (if "john doe" was entered).

   • Row 3: Jane Smith (if "JANE SMITH" was entered).

   • Row 4: Alan Brown (if "aLaN bRoWn" was entered).

Eleventh Formula: NOW

The NOW formula returns the current date and time based on your system's clock.

How to Apply NOW:

1. Purpose: To display the current date and time in the sheet, useful for tracking when the data was updated.

2. Formula: =NOW()

Step-by-Step Application:

1. In an empty cell (e.g., P1), type: Current Date & Time.

2. In the cell below it (e.g., P2), type the formula: =NOW().

3. Press Enter.

4. The result will show the current date and time in the format DD/MM/YYYY HH:MM:SS (e.g., 03/12/2024 14:30:45).

Twelfth Formula: ROUND

The ROUND formula is used to round numbers to a specified number of digits.

How to Apply ROUND:

1. Purpose: To round the "Salary" column values to the nearest thousand for better readability.

2. Formula: =ROUND(E2, -3)

   • E2: Refers to the "Salary" of the first employee.

   • -3: Indicates rounding to the nearest thousand (negative numbers for left of the decimal).

Step-by-Step Application:

1. In a new column (e.g., P1), type: Rounded Salary.

2. In the cell below it (e.g., P2), type the formula: =ROUND(E2, -3).

3. Press Enter.

4. Drag the formula down to apply it to other rows (P3, P4, etc.).

5. The results will show:

   • Row 2: 50000 (already rounded).

   • Row 3: 65000 (already rounded).

   • Row 4: 80000 (already rounded).

Thirteenth Formula: TEXT

The TEXT formula is used to format numbers, dates, and other values into a specific format as text.

How to Apply TEXT:

1. Purpose: To format the "Joining Date" column into a more readable format, such as DD-MMM-YYYY (e.g., 01-Jan-2020).

2. Formula: =TEXT(D2, "DD-MMM-YYYY")

   • D2: Refers to the "Joining Date" column.

   • "DD-MMM-YYYY": Specifies the desired date format.

Step-by-Step Application:

1. In a new column (e.g., Q1), type: Formatted Joining Date.

2. In the cell below it (e.g., Q2), type the formula: =TEXT(D2, "DD-MMM-YYYY").

3. Press Enter.

4. Drag the formula down to apply it to other rows (Q3, Q4, etc.).

5. The results will show:

   • Row 2: 01-Jan-2020 (from 01-01-2020).

   • Row 3: 15-Jul-2018 (from 15-07-2018).

   • Row 4: 23-Mar-2015 (from 23-03-2015).

Fourteenth Formula: RANK

The RANK formula is used to determine the rank of a number within a list, either in ascending or descending order.

How to Apply RANK:

1. Purpose: To rank employees based on their "Salary" in descending order (highest salary = rank 1).

2. Formula: =RANK(E2, E$2:E$4, 0)

   • E2: Refers to the "Salary" of the first employee.

   • E$2:E$4: Refers to the range of all salaries to rank.

   • 0: Specifies descending order (use 1 for ascending).

Step-by-Step Application:

1. In a new column (e.g., R1), type: Salary Rank.

2. In the cell below it (e.g., R2), type the formula: =RANK(E2, E$2:E$4, 0).

3. Press Enter.

4. Drag the formula down to apply it to other rows (R3, R4, etc.).

5. The results will show:

   • Row 2: 3 (50000 is the 3rd highest salary).

   • Row 3: 2 (65000 is the 2nd highest salary).

   • Row 4: 1 (80000 is the highest salary).

Fifteenth Formula 

How to Apply PMT for Different Individuals:

We’ll extend the table to include three new columns:

1. Loan Amount: The loan amount each individual plans to take.

2. Annual Interest Rate: The interest rate for each loan.

3. Loan Duration (Years): The duration of the loan in years.

Step-by-Step Setup:

1. Add three new columns to the table:

   • Loan Amount (Column S): Enter loan amounts for each person (e.g., 500000, 700000, 300000).

   • Interest Rate (Column T): Enter the annual interest rate for each person (e.g., 7%, 6.5%, 8%).

   • Loan Duration (Years) (Column U): Enter the loan duration in years for each person (e.g., 10, 15, 5).

2. Add a column for the Monthly Payment (Column V) and use the following formula:

   • Formula: =PMT(T2/12, U2*12, -S2)

     • T2: Refers to the annual interest rate for the individual.

     • /12: Converts the annual rate to a monthly rate.

     • U2*12: Converts the duration in years to the total number of monthly payments.

     • -S2: Refers to the loan amount (negative to represent cash outflow).

Sixteenth Formula: INDEX

The INDEX formula is used to return the value of a cell at a specific row and column in a given range.

How to Apply INDEX:

1. Purpose: To retrieve specific data from the table, such as the "Name" of the employee in the second row of the table.

2. Formula: =INDEX(B2:B4, 2)

   • B2:B4: The range of cells in the "Name" column.

   • 2: Refers to the row number within the selected range.

Step-by-Step Application:

1. In a new column (e.g., X1), type: Employee by Row.

2. In the cell below it (e.g., X2), type the formula: =INDEX(B2:B4, 2).

3. Press Enter.

4. The result will show:

   • Jane Smith because she is the second entry in the "Name" column.

Advanced Use Case:

To retrieve data from a table with multiple rows and columns, use both row and column numbers:

• Formula: =INDEX(A2:G4, 2, 3)

  • A2:G4: The entire table range.

  • 2: Refers to the second row.

  • 3: Refers to the third column (in this case, "Age").

This will return the "Age" of the second employee (32 in the example).

Pairing with INDEX:

You can combine MATCH with INDEX to automate data retrieval:

• Example: If "Jane Smith" is in position 2, use that position to get her salary:

  • =INDEX(E2:E4, MATCH("Jane Smith", B2:B4, 0))

  • This will fetch the salary associated with "Jane Smith" without manual lookups.

Eighteenth Formula: IFERROR

The IFERROR formula is used to handle errors gracefully. Instead of showing error messages like #N/A, #DIV/0!, or others, it displays a custom message or performs an alternative action.

How to Apply IFERROR:

1. Purpose: To prevent the #N/A error in the MATCH formula when the value isn’t found.

2. Formula: =IFERROR(MATCH("Jane Smith", B2:B4, 0), "Not Found")

   • MATCH("Jane Smith", B2:B4, 0): Tries to find "Jane Smith" in the range.

   • "Not Found": The value to return if an error occurs.

Step-by-Step Application:

1. In a new column (e.g., Z1), type: Error-Handled Match.

2. In the cell below it (e.g., Z2), type the formula: =IFERROR(MATCH("Jane Smith", B2:B4, 0), "Not Found").

3. Press Enter.

4. The result will show:

   • 2 if "Jane Smith" is found in the second row.

   • "Not Found" if "Jane Smith" isn’t in the range.

Use Cases:

1. Preventing Error Messages in Reports:

   • Display user-friendly messages instead of errors, improving the readability of dashboards or reports.

2. Data Validation:

   • Check for missing data and flag errors with a custom message.

3. Combining with Other Formulas:

   • Use with VLOOKUP, INDEX, or any formula prone to errors.

Nineteenth Formula: COUNTIFS

The COUNTIFS formula counts the number of rows that meet multiple conditions in a dataset.

How to Apply COUNTIFS:

1. Purpose: To count the number of employees with a "Performance Score" above 80% and in the "Sales" department.

2. Formula: =COUNTIFS(G2:G4, ">80%", F2:F4, "Sales")

   • G2:G4: Range of "Performance Score."

   • ">80%": Condition for the performance score.

   • F2:F4: Range of "Department."

   • "Sales": Condition for the department.

Step-by-Step Application:

1. In a new column (e.g., AA1), type: High Performers in Sales.

2. In the cell below it (e.g., AA2), type the formula: =COUNTIFS(G2:G4, ">80%", F2:F4, "Sales").

3. Press Enter.

4. The result will show:

   • 1 if only one employee meets both conditions.

   • 0 if no employees meet both conditions.

Twentieth Formula: SUMIFS

The SUMIFS formula calculates the sum of values that meet multiple conditions.

How to Apply SUMIFS:

1. Purpose: To calculate the total salary of employees with a "Performance Score" above 80% and in the "Sales" department.

2. Formula: =SUMIFS(E2:E4, G2:G4, ">80%", F2:F4, "Sales")

   • E2:E4: Range of "Salary" to sum.

   • G2:G4: Range of "Performance Score."

   • ">80%": Condition for the performance score.

   • F2:F4: Range of "Department."

   • "Sales": Condition for the department.

Step-by-Step Application:

1. In a new column (e.g., AB1), type: Total Salary of High Performers in Sales.

2. In the cell below it (e.g., AB2), type the formula: =SUMIFS(E2:E4, G2:G4, ">80%", F2:F4, "Sales").

3. Press Enter.

4. The result will show:

   • The sum of salaries for employees who meet both conditions.

Use Cases:

1. Financial Analysis:

   • Calculate total expenses, revenues, or salaries based on multiple criteria.

2. Performance Evaluation:

   • Sum up bonuses or salaries for employees meeting performance benchmarks.

3. Sales and Marketing:

   • Add up revenues for specific products in selected regions or periods.

Twenty-First Formula: SUBTOTAL

The SUBTOTAL formula is used to perform calculations (like SUM, AVERAGE, COUNT, etc.) on a filtered range of data. It is dynamic and updates based on visible rows when filters are applied.

How to Apply SUBTOTAL:

1. Purpose: To calculate the total salary of employees visible after applying filters.

2. Formula: =SUBTOTAL(9, E2:E4)

   • 9: Represents the function for SUM (other codes are available for different calculations).

   • E2:E4: The range of the "Salary" column.

Step-by-Step Application:

1. Apply a filter to your table:

   • Highlight the table, go to the "Data" tab, and click "Filter."

   • Use filters to show only specific rows (e.g., filter by Department or Performance Score).

2. In an empty cell (e.g., AC1), type: Filtered Salary Total.

3. In the cell below it (e.g., AC2), type the formula: =SUBTOTAL(9, E2:E4).

4. Press Enter.

5. The result will dynamically adjust based on the visible rows after filtering.

Use Cases:

1. Dynamic Summaries:

   • Instantly update totals for filtered data, such as filtered departments or performance groups.

2. Flexible Reporting:

   • Use SUBTOTAL in dashboards to display changing totals as filters are applied.

3. Avoiding Hidden Rows:

   • Unlike SUM, SUBTOTAL ignores rows hidden by filters, making it more accurate for filtered datasets.

Twenty-Second Formula: UNIQUE

The UNIQUE formula extracts unique values from a range, eliminating duplicates.

Formula-Based Workaround (Array Formula):

Use an array formula for unique values:

1. Type the formula:

   • =IF(COUNTIF($F$2:F2, F2)=1, F2, "")

   • Drag it down to populate unique values.

2. Filter out blanks manually if needed.

How to Apply UNIQUE:

1. Purpose: To list all unique departments from the "Department" column.

2. Formula: =UNIQUE(F2:F4)

   • F2:F4: The range of the "Department" column.

Step-by-Step Application:

1. In a new column (e.g., AD1), type: Unique Departments.

2. In the cell below it (e.g., AD2), type the formula: =UNIQUE(F2:F4).

3. Press Enter.

4. The result will display the unique departments:

   • Sales

   • HR

   • IT

Twenty-Fourth Formula: OR

The OR formula is a logical function used to check if at least one condition is TRUE. It returns TRUE if any condition is true, otherwise FALSE.

How to Apply OR:

1. Purpose: To check if an employee has a "Performance Score" above 80% or is in the "Sales" department.

2. Formula: =OR(G2>80%, F2="Sales")

   • G2>80%: Checks if the performance score is greater than 80%.

   • F2="Sales": Checks if the department is "Sales".

Step-by-Step Application:

1. In a new column (e.g., AF1), type: High Performance or Sales?.

2. In the cell below it (e.g., AF2), type the formula: =OR(G2>80%, F2="Sales").

3. Press Enter.

4. Drag the formula down to apply it to other rows (AF3, AF4, etc.).

5. The results will show:

   • TRUE if at least one of the conditions is met (e.g., performance score > 80% or being in the Sales department).

   • FALSE if neither condition is met.

Twenty-Fifth Formula: TEXTJOIN

The TEXTJOIN formula combines multiple text values into one string, separated by a delimiter (e.g., a comma, space, or custom character). It is particularly useful when you want to combine text while ignoring blank cells.

How to Apply TEXTJOIN:

1. Purpose: To combine employee names and departments into a single string, separated by commas.

2. Formula: =TEXTJOIN(", ", TRUE, B2, F2)

   • ", ": Specifies the delimiter (comma and space in this case).

   • TRUE: Ignores blank cells.

   • B2, F2: Refers to the cells containing the employee name and department.

Step-by-Step Application:

1. In a new column (e.g., AG1), type: Name and Department Combined.

2. In the cell below it (e.g., AG2), type the formula: =TEXTJOIN(", ", TRUE, B2, F2).

3. Press Enter.

4. Drag the formula down to apply it to other rows (AG3, AG4, etc.).

5. The results will show:

   • Row 2: John Doe, Sales

   • Row 3: Jane Smith, HR

   • Row 4: Alan Brown, IT

FEE REFUND IF NO THREE DIGIT RANK IN CUET NEET JEE

Contact us: 9315856603

Email : arcomedeng@gmail.com

Centre: E1 Vasant Kunj│ Kidwai Nagar Market 

PERFECT FOR HOME TUITIONs

Terms & Conditions  │Privacy Policy  │Site Map © 2022 - All Rights Reserved

BWR6P4VT 

OTR ID - 123000007161056 & Fortuner@1

FVT SUBJECT - CHEMISTRY

FVT COLOUR - RED