Excel Worksheet Program - Solved
Math and Trigonometric
SUM - Syntax: =SUM(A1:A5) - Adds values in cells A1 through A5
ROUND - Syntax: =ROUND(A1, 2) - Rounds number in A1 to 2 decimal places
ABS - Syntax: =ABS(A1) - Returns absolute value of A1
POWER - Syntax: =POWER(2,3) - Returns 2 raised to the power 3
SQRT - Syntax: =SQRT(16) - Returns square root of 16
PI - Syntax: =PI() - Returns the value of pi
SIN - Syntax: =SIN(PI()/2) - Returns sine of π/2
String
LEN - Syntax: =LEN(A1) - Returns the length of text in A1
CONCATENATE - Syntax: =CONCATENATE(A1, B1) - Combines values in A1 and B1
LEFT - Syntax: =LEFT(A1, 4) - Extracts first 4 characters from A1
RIGHT - Syntax: =RIGHT(A1, 3) - Extracts last 3 characters from A1
MID - Syntax: =MID(A1, 2, 3) - Extracts 3 characters from A1 starting at 2
LOWER - Syntax: =LOWER(A1) - Converts A1 to lowercase
UPPER - Syntax: =UPPER(A1) - Converts A1 to uppercase
Date and Time
TODAY - Syntax: =TODAY() - Returns current date
NOW - Syntax: =NOW() - Returns current date and time
DAY - Syntax: =DAY(A1) - Returns the day of a date in A1
MONTH - Syntax: =MONTH(A1) - Returns the month of a date
YEAR - Syntax: =YEAR(A1) - Returns the year of a date
DATEDIF - Syntax: =DATEDIF(A1, B1, "d") - Returns difference in days between two
dates
EDATE - Syntax: =EDATE(A1, 1) - Returns date one month after A1
Statistical
AVERAGE - Syntax: =AVERAGE(A1:A5) - Calculates average of A1 through A5
MEDIAN - Syntax: =MEDIAN(A1:A5) - Returns median value
MODE - Syntax: =MODE(A1:A5) - Returns most frequent value
STDEV - Syntax: =STDEV(A1:A5) - Returns standard deviation
VAR - Syntax: =VAR(A1:A5) - Returns variance
MAX - Syntax: =MAX(A1:A5) - Returns maximum value
MIN - Syntax: =MIN(A1:A5) - Returns minimum value
Text
TEXT - Syntax: =TEXT(A1, "0.00") - Formats number in A1
TRIM - Syntax: =TRIM(A1) - Removes extra spaces from A1
CLEAN - Syntax: =CLEAN(A1) - Removes non-printable characters
FIND - Syntax: =FIND("e", A1) - Finds position of 'e' in A1
SUBSTITUTE - Syntax: =SUBSTITUTE(A1, "old", "new") - Replaces 'old' with 'new'
REPT - Syntax: =REPT(A1, 3) - Repeats content of A1 3 times
TEXTJOIN - Syntax: =TEXTJOIN(", ", TRUE, A1:A3) - Joins text with delimiter
Financial
PMT - Syntax: =PMT(5%/12, 60, -10000) - Calculates loan payment
FV - Syntax: =FV(5%/12, 60, -100, -10000) - Calculates future value
PV - Syntax: =PV(5%/12, 60, -100) - Calculates present value
RATE - Syntax: =RATE(60, -100, 5000) - Returns interest rate
NPER - Syntax: =NPER(5%/12, -100, 5000) - Returns number of periods
NPV - Syntax: =NPV(10%, A1:A5) - Calculates net present value
IRR - Syntax: =IRR(A1:A5) - Returns internal rate of return
Lookup and Reference
VLOOKUP - Syntax: =VLOOKUP(1001, A2:C10, 2, FALSE) - Looks up 1001 in first column
HLOOKUP - Syntax: =HLOOKUP("Q1", A1:D4, 2, FALSE) - Looks up 'Q1' in top row
INDEX - Syntax: =INDEX(A1:C3, 2, 3) - Returns value at row 2, column 3
MATCH - Syntax: =MATCH(39, A1:A5, 0) - Finds 39 in range
OFFSET - Syntax: =OFFSET(A1, 2, 1) - Returns value offset from A1
CHOOSE - Syntax: =CHOOSE(2, "Red", "Blue", "Green") - Returns second item
XLOOKUP - Syntax: =XLOOKUP(1001, A2:A10, B2:B10) - Modern lookup function
Logical
IF - Syntax: =IF(A1>10, "Yes", "No") - Returns Yes if A1>10
AND - Syntax: =AND(A1>0, B1<5) - Returns TRUE if both are TRUE
OR - Syntax: =OR(A1>0, B1<5) - Returns TRUE if either is TRUE
NOT - Syntax: =NOT(A1>10) - Negates condition
IFERROR - Syntax: =IFERROR(A1/B1, "Error") - Returns 'Error' if division fails
IFS - Syntax: =IFS(A1>90,"A", A1>80,"B") - Multiple IFs
SWITCH - Syntax: =SWITCH(A1, 1,"One", 2,"Two") - Replaces nested IFs
