notes-shivam

EXCEL FORMULAS

3 min read

Microsoft Excel Formulas

Microsoft Excel is a powerful spreadsheet application that allows you to perform a variety of tasks, including data analysis, financial modeling, and scientific calculations. One of the most important features of Excel is its ability to use formulas to automate calculations and manipulate data.

Here are some of the most common types of Excel formulas:

1. Basic formulas:

  • SUM: This function adds up a range of cells. For example, =SUM(A1:A10) will add the values in cells A1 through A10.

  • AVERAGE: This function calculates the average of a range of cells. For example, =AVERAGE(B1:B10) will calculate the average of the values in cells B1 through B10.

  • COUNT: This function counts the number of cells in a range that contain numbers. For example, =COUNT(C1:C10) will count the number of cells in the range C1 to C10 that contain numbers.

  • MIN: This function finds the smallest value in a range of cells. For example, =MIN(D1:D10) will find the smallest value in the range D1 to D10.

  • MAX: This function finds the largest value in a range of cells. For example, =MAX(E1:E10) will find the largest value in the range E1 to E10.

2. Logical formulas:

  • IF: This function allows you to perform different calculations based on a specific condition. For example, the following formula will return “Pass” if the value in cell A1 is greater than 70, and “Fail” if it is not: =IF(A1>70,“Pass”,“Fail”).

  • AND: This function allows you to combine multiple conditions. For example, the following formula will only return “Pass” if the value in cell A1 is greater than 70 and the value in cell B1 is greater than 80: =IF(AND(A1>70,B1>80),“Pass”,“Fail”).

  • OR: This function allows you to test for any of a number of conditions. For example, the following formula will return “Pass” if the value in cell A1 is greater than 70 or the value in cell B1 is greater than 80: =IF(OR(A1>70,B1>80),“Pass”,“Fail”).

3. Lookup and reference formulas:

  • VLOOKUP: This function allows you to look up a value in one table and return a corresponding value from another table. For example, the following formula will return the value in column B of the table in range B2:C11 that has the same value in column A as the value in cell A1: =VLOOKUP(A1,B2:C11,2,FALSE).

  • XLOOKUP: This is a newer function that is similar to VLOOKUP, but it is more flexible and easier to use. For example, the following formula will return the value in column B of the table in range B2:C11 that has the same value in column A as the value in cell A1: =XLOOKUP(A1,B2:B11,C2:C11).

  • INDEX MATCH: This is a powerful combination of functions that can be used to look up values in tables of any size or shape. It is more complex than VLOOKUP or XLOOKUP, but it is more versatile.

4. Date and time formulas:

  • TODAY: This function returns the current date.

  • NOW: This function returns the current date and time.

  • DATEDIF: This function calculates the difference between two dates.

5. Text formulas:

  • CONCATENATE: This function joins two or more text strings together. For example, the following formula will combine the text in cells A1 and B1: =CONCATENATE(A1,” “,B1).

  • LEFT: This function returns the leftmost characters in a text string. For example, the following formula will return the first five characters in cell A1: =LEFT(A1,5). [

Graph View

Backlinks