Scribd
Upload
9 views3 pages

Methods Excel Revision

The document provides a comprehensive checklist of Excel formulas, including functions for calculating totals, differences, products, percentages, averages, and various types of interest. It includes syntax examples for each function and demonstrates how they can be applied to specific cell ranges. Additionally, it offers guidance on using the F4 key for locking cell references.

Uploaded by

babisaikia06
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views3 pages

Methods Excel Revision

The document provides a comprehensive checklist of Excel formulas, including functions for calculating totals, differences, products, percentages, averages, and various types of interest. It includes syntax examples for each function and demonstrates how they can be applied to specific cell ranges. Additionally, it offers guidance on using the F4 key for locking cell references.

Uploaded by

babisaikia06
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

METHODS REVISION

EXCEL REVISION

FORMULA checklist

TOTAL (SUM) MAXIFS COUNTBLANK


DIFFERENCE MINIFS DIVISION
PRODUCT MEAN SIMPLE INTEREST
PERCENTAGE MODE COUMPOUND
AVERAGE MEDIAN INTEREST
GRADE (IF) COUNT SUMIF
MAX COUNTIF VLOOKUP
MIN COUNTA HLOOKUP
LOOKUP
DAY, MONTH, YEAR
PROFIT/LOSS

Go to next page for formulas

For lock F4 key ----dollar sign


METHODS REVISION

Function Name Excel Formula Syntax

TOTAL (SUM) =SUM(range)

DIFFERENCE =number1 - number2 / for removing negative sign: =abs(number1 - number2)

PRODUCT =PRODUCT(range)

PERCENTAGE =(part/total)*100

AVERAGE =AVERAGE(range)

GRADE (IF) =IF(logical_test, "True Result", "False Result")

MAX =MAX(range)

MIN =MIN(range)

MAXIFS =MAXIFS(max_range, criteria_range1, criteria1, ...)

MINIFS =MINIFS(min_range, criteria_range1, criteria1, ...)

MEAN =AVERAGE(range)

MODE =MODE(range)

MEDIAN =MEDIAN(range)

COUNT =COUNT(range)

COUNTIF =COUNTIF(range, criteria)

COUNTA =COUNTA(range)

COUNTBLANK =COUNTBLANK(range)

DIVISION =number1 / number2

SIMPLE INTEREST = (Principal * Rate * Time) / 100

COMPOUND INTEREST =Principal * (1 + Rate/100) ^ Time

SUMIF =SUMIF(range, criteria, [sum_range])

VLOOKUP =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

HLOOKUP =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

LOOKUP =LOOKUP(lookup_value, lookup_vector, result_vector)

DAY, MONTH, YEAR =DAY(serial_number), =MONTH(serial_number), =YEAR(serial_number)

PROFIT/LOSS =Selling Price – Cost Price

How the formulae will look like after cell values.


METHODS REVISION
Function Name Excel Formula Example

TOTAL (SUM) =SUM(A1:A10)

DIFFERENCE =A2-A3 / for removing negative sign: =abs(A2-A3)

PRODUCT =PRODUCT(A1:A3)

PERCENTAGE =(A1/A2)*100

AVERAGE =AVERAGE(A1:A10)

GRADE (IF) =IF(A1>=90, "A", "B")

MAX =MAX(A1:A10)

MIN =MIN(A1:A10)

MAXIFS =MAXIFS(A1:A10, B1:B10, ">70")

MINIFS =MINIFS(A1:A10, B1:B10, "<50")

MEAN =AVERAGE(A1:A10)

MODE =MODE(A1:A10)

MEDIAN =MEDIAN(A1:A10)

COUNT =COUNT(A1:A10)

COUNTIF =COUNTIF(A1:A10, ">50")

COUNTA =COUNTA(A1:A10)
Nin empty cells
COUNTBLANK =COUNTBLANK(A1:A10)

DIVISION =A1/B1

SIMPLE INTEREST =(P*R*T)/100 or =(A1*A2*A3)/100

COMPOUND INTEREST =P*(1+R/100)^T or =A1*(1+A2/100)^A3

SUMIF =SUMIF(A1:A10, ">50")

VLOOKUP =VLOOKUP(lookup_value, table_array, col_index, FALSE)

HLOOKUP =HLOOKUP(lookup_value, table_array, row_index, FALSE)

LOOKUP =LOOKUP(lookup_value, lookup_vector, result_vector)

DAY, MONTH, YEAR =DAY(A1), =MONTH(A1), =YEAR(A1)

PROFIT/LOSS =Selling_Price - Cost_Price or =A1 - A2

You might also like