Excel Formulas For Everyday Usages

Posted: January 11, 2025 Updated: January 11, 2025

 Format cells with different colors.

For rows

  • =MOD(ROW(),2)=0
  • =MOD(ROW(),2)=1

For columns

  • =MOD(COLUMN(),2)=0
  • =MOD(COLUMN(),2)=1

=COUNTIF(C3:C500,"*ISD*")
=COUNTIF(D3:D500,550)
=SUM(A3:A10)

1. *SUM*: Adds a range of numbers.
   - =SUM(A1:A10)

2. *AVERAGE*: Calculates the average of a range.
   - =AVERAGE(B1:B10)

3. *COUNT*: Counts the number of cells that contain numbers.
   - =COUNT(C1:C10)

4. *COUNTA*: Counts non-empty cells.
   - =COUNTA(D1:D10)

5. *MAX*: Finds the maximum value in a range.
   - =MAX(E1:E10)

6. *MIN*: Finds the minimum value in a range.
   - =MIN(F1:F10)

7. *IF*: Performs a logical test and returns values based on the result.
   - =IF(G1>10, "Yes", "No")

8. *VLOOKUP*: Looks up a value in a table and returns a corresponding value.
   - =VLOOKUP(H1, A1:B10, 2, FALSE)

9. *HLOOKUP*: Looks up a value in the top row of a table and returns a corresponding value.
   - =HLOOKUP(I1, A1:E5, 2, FALSE)

10. *INDEX*: Returns a value from a specified position in a range.
    - =INDEX(A1:B10, 2, 1)

11. *MATCH*: Returns the position of a value in a range.
    - =MATCH("Apple", A1:A10, 0)

12. *CONCATENATE* (or *&*): Joins two or more text strings.
    - =CONCATENATE(A1, " ", B1) or =A1 & " " & B1

13. *TRIM*: Removes extra spaces from text.
    - =TRIM(A1)

14. *LEFT*: Extracts a specified number of characters from the left.
    - =LEFT(A1, 3)

15. *RIGHT*: Extracts a specified number of characters from the right.
    - =RIGHT(A1, 3)

16. *MID*: Extracts a substring from a string.
    - =MID(A1, 2, 3)

17. *TODAY*: Returns the current date.
    - =TODAY()

18. *NOW*: Returns the current date and time.
    - =NOW()

19. *PMT*: Calculates the payment for a loan based on constant payments and a constant interest rate.
    - =PMT(rate, nper, pv)

20. *ROUND*: Rounds a number to a specified number of digits.
    - =ROUND(A1, 2)

=SUMIF(F:F, "Pending", H:H)
Explanation:
  • F:F: This is the range where you are checking for the word "Pending" (your F6 column).
  • "Pending": This is the condition, i.e., the word you're searching for in column F.
  • H:H: This is the range where you sum the values if the condition is met (your H6 column).
How It Works:
  • The formula looks at all rows in column F for the word "Pending."
  • If "Pending" exists in a row of column F, it sums up the corresponding value in column H from the same row.

Label:
Post Comment

Comments

Post a Comment