Excel Formulas Menu: Insert Function, AUTOSUM, AVG, PER, MAX, MIN, COUNT, IF, Date & Time, Round

Formulas Menu Overview

1. Insert Function

• Purpose: To access a list of all available functions and insert them into a cell with assistance for choosing arguments.
• Usage:
  • Access: Click Formulas > Insert Function (or press Shift + F3).
  • Function Dialog Box:
    • Search for Functions: Enter a brief description of what you want to do (e.g., "average") to find relevant functions.
    • Browse Functions: Browse through categories of functions like Financial, Date & Time, Math & Trig, etc.
    • Insert Function: Select the desired function and click OK. The Function Arguments dialog box will help you input the required arguments.

2. AutoSum

• Purpose: To quickly sum a range of numbers without manually typing the SUM formula.
• Usage:
  • Access: Click Formulas > AutoSum (or press Alt + =).
  • Automatic Range: Excel automatically selects the most likely range of cells to sum based on the location of the cursor. Verify the range and adjust if necessary.
  • Complete the Formula: Press Enter to complete the formula and display the sum.

3. AVERAGE

• Purpose: To calculate the average (mean) of a set of numbers.
• Usage:
  • Access: Click Formulas > More Functions > Statistical > AVERAGE.
  • Formula: =AVERAGE(range)
  • Example: =AVERAGE(B2:B10) calculates the average of the values in cells B2 through B10.

4. PER (Percent)

• Purpose: This is typically not a direct function but often referred to when calculating percentages. You can use basic formulas to achieve this.
• Usage:
  • Calculate Percentages: To find a percentage, use a formula like =part/total and format the cell as a percentage.
  • Example: To find what percentage 50 is of 200, use =50/200 and format the result cell as a percentage.

5. MAX

• Purpose: To find the highest value in a range of numbers.
• Usage:
  • Access: Click Formulas > More Functions > Statistical > MAX.
  • Formula: =MAX(range)
  • Example: =MAX(C2:C10) finds the highest value in cells C2 through C10.

6. MIN

• Purpose: To find the lowest value in a range of numbers.
• Usage:
  • Access: Click Formulas > More Functions > Statistical > MIN.
  • Formula: =MIN(range)
  • Example: =MIN(D2:D10) finds the lowest value in cells D2 through D10.

7. COUNT

• Purpose: To count the number of cells that contain numeric values in a range.
• Usage:
  • Access: Click Formulas > More Functions > Statistical > COUNT.
  • Formula: =COUNT(range)
  • Example: =COUNT(E2:E10) counts the number of cells with numeric values in the range E2 through E10.

8. IF

• Purpose: To perform a logical test and return one value if the condition is true and another value if it is false.
• Usage:
  • Access: Click Formulas > More Functions > Logical > IF.
  • Formula: =IF(logical_test, value_if_true, value_if_false)
  • Example: =IF(F2>100, "Over 100", "100 or less") returns "Over 100" if the value in cell F2 is greater than 100; otherwise, it returns "100 or less".

9. Date & Time

• Purpose: To insert and manipulate date and time values.
• Usage:
  • Access: Click Formulas > More Functions > Date & Time.
  • Common Functions:
    • TODAY(): Returns the current date.
    • NOW(): Returns the current date and time.
    • DATE(year, month, day): Creates a date from individual year, month, and day values.
    • DAYS(start_date, end_date): Calculates the number of days between two dates.

10. ROUND

• Purpose: To round a number to a specified number of digits.
• Usage:
  • Access: Click Formulas > More Functions > Math & Trig > ROUND.
  • Formula: =ROUND(number, num_digits)
  • Example: =ROUND(G2, 2) rounds the value in cell G2 to two decimal places.