Excel Formulas

·

Calculation

  • AVERAGE – returns the average
  • CEILING – rounds a number up
  • CONCAT – concatenates a list or range of text strings
  • COUNT – counts the number of cells
  • COUNTA – counts the number of cells (excl. empty)
  • COUNTBLANK – counts the number of empty cells
  • FLOOR – rounds a number down
  • MAX – returns the largest value
  • MIN – returns the smallest number
  • POWER – returns the result of a number raised to a power
  • SUBTOTAL – returns a subtotal in a list or database
  • SUM – adds all the numbers

Text

  • REPLACE – replaces part of a text
  • SUBSTITUTE – replaces existing text with new text
  • TRIM – removes all spaces from text (excl. single spaces between words)
  • LEFT – returns the specified number of characters from the start of a text string
  • RIGHT – returns the specified number of characters from the end of a text string
  • MID – returns the characters from the middle of a text string, given a starting position and length
  • LEN – returns the number of characters in a text string
  • UPPER – converts a text string to all uppercase letters
  • LOWER – converts all letters in a text string to lowercase
  • PROPER – converts a text string to proper case

Date and Time

  • TODAY – returns the current date formatted as a date
  • DAY – returns the day of the month
  • NOW – returns the current date and time formatted as a date and time
  • SECOND – returns the second
  • DATEDIF – calculates the date difference (start date,end date,”d”)

Lookup

  • VLOOKUP – looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • HLOOKUP – looks for a value in the top row of a table and returns the value in the same column from a row you specify

Condition

  • IF – checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *