Functions in MS Excel

Functions
Len Emonth Now()

Find SLN() PMT
Mid Counts Proper
Right Subtotal ISNA
Left FV Iferror
If PV Value
Sum Datedif Datevalue
Sumif Today Append
Sumifs Year Average
Count Month Averageif
Counta Day() Averageifs
Countif Now() And
Countifs Trim() Or
Order of Precedence Rules
Order of Precedence Example Resulting Value Explanation
1. Operation in = A1*(3 + 5) If A1=2, the Excel first performs the

parenthesis resulting value is addition of 3+5 even though
2*(3+5), or multiplication has a higher
2*8=16 precedence than addition,
because the addition
operation is enclosed in
parentheses.
2. Exponentiation = 3 *A1 ^ 3 If A1=2, the Excel first performs the
resulting value is exponential operation of
3*2^3, or 3*8=24 cubing A1, and then performs
the multiplication.
3. Multiplication and =A1 + B2 * C3 If A1=2, B2=3 , Excel first multiplies B2 by
division from left to and C3=10, the C3 and then adds the result
right resulting value is to A1.
2+3*10, or
2+30=32
4. Addition and =A1-B2+C3/10 If A1=2, B2=3 , Excel first divides C3 by 10,
subtraction form left to and C3=10, the then subtract B2 from A1,
right resulting value is and finally adds this value to
2-3+10/10, or - the quotient.
1+1=0
Excel error messages
Error Description
Message
##### Insufficient width in cell to display
numerical data, or negative date/time
#NAME? Unrecognized text in a formula
#N/A No Answer
#REF! Invalid cell reference
#VALUE! Wrong argument type or operand
#NUM! Invalid numeric values in a formula or
function
#DIV/0! Division by zero
Absolute and Relative References
Referenc Meaning
e Style
$A$1 Both the column and row reference are fixed.
Neither will be incremented or changed during a
copy or fill operation.
$A1 Only the column reference is fixed. It will not
change during a fill or copy, but the row will
change.
A$1 Only the row reference is fixed. It will not change
during a fill or copy, but the column will change.
AND
Returns TRUE if all its arguments are TRUE; returns FALSE if
one or more argument is FALSE.
Syntax AND(logical1,logical2, ...)
OR
Returns TRUE if any argument is TRUE; returns FALSE if all
arguments are FALSE.
Syntax OR(logical1,logical2,...)
TRUTH TABLE
Logical First Second Result

Operator Condition Condition
AND True True True
AND True False False
AND False False False
AND False True False
OR True True True
OR True False True
OR False True True
OR False False False
Nested IF Function - example
If Score is Then return
• Greater than 97 Oustanding
• From 94 to 97 Very Satisfactory
• From 87 to 93 Very Good
• From 86 to 81 Good
• From 80 to 75 Passed
• Below 75 Failed
Syntax:
=IF(A2>97,"Oustanding",IF(A2>93,"Very Satisfactory",IF(A2>86,"Very
Good",IF(A2>80,"Good",IF(A2>74,"Fair","Failed")))))
CONCATENATE - Joins several text strings
into one text string.
Syntax
CONCATENATE (text1, text2,...)

Text1, text2, ... are 1 to 30 text items to be joined
into a single text item. The text items can be text
strings, numbers, or single-cell references.
Remarks
The "&" operator can be used instead of
CONCATENATE to join text items.
DATE()
Returns the sequential serial number that represents a particular date. If the
cell format was General before the function was entered, the result is
formatted as a date.
Syntax
DATE(year,month,day)
Year() returns the year

Month() returns the month
Day() returns the day
Today() returns today’s date
Now() returns the serial number of the current date and time.
Note:
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900.
DATEDIF(TRANS DATE, CUT-OFF DATE, INTERVAL)
computes the date difference between two dates
Interval
"y" The number of complete years in the period.
"m" The number of complete months in the period.
"d" The number of days in the period.
"md" The difference between the days in start_date and end_date. The
months and years of the dates are ignored.
"ym" The difference between the months in start_date and end_date. The
days and years of the dates are ignored
"yd" The difference between the days of start_date and end_date. The
years of the dates are ignored.
The End

Functions in MS Excel

Uploaded by

Copyright:

Available Formats

Functions in MS Excel

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Functions in MS Excel

Uploaded by

Copyright:

Available Formats

Functions

Len Emonth Now()

1. Operation in = A1*(3 + 5) If A1=2, the Excel first performs the

Syntax AND(logical1,logical2, ...)

Logical First Second Result

CONCATENATE (text1, text2,...)

Year() returns the year

"y" The number of complete years in the period.

"m" The number of complete months in the period.

"d" The number of days in the period.

You might also like