Sum

Avg Min 78 63.66666667 85 74 78 85 74 7 85 7 573 148

Max 7

Upper 85 computer COMPUTER

Lower computer

Count 45 78 89 85 74 g r e d 5

Counta 9

Product

month Countif 78 10-Jun name 85 6 VISHAL 6630 ROSHAN 4165

mon P A

tue P P

wed P P

thu P P

DA 0 1

DP 4 3

TEXT FUNCTION LEN

DOLLAR
converts a number to text format and applies a currency symbol.
EXAMPLE

50 $50.00

Original Number 10 10 10 10 10.25 10.25 10.25 10.25

Converted To DOLLER

TRIM
Removes all spaces from text except for single spaces between words.
EXAMLPES A B C D C O M P T E X T ABCD

CONCATENATE
To join text items
EXAMPLE MS EXCEL MAHENDRA ARUSH RANE MS EXCEL

TUSAHR SHINDE

CLEAN
Removes all nonprintable characters from text.
EXAMPLE CLEAN FUNCTION TABLE CLEAN

TEXT FUNCTION

ISBlank
This formula checks to see if an error condition
EXAMPLE

MS OFFICE

IFERROR
EXAMPLE Quota 210 55 12 R

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the

RANK
Returns the rank of a number in a list of numbers
EXAMLPES Values 1200 800 700 650 300 200

INFO
Returns information about the current operating environment.
System Information Current directory Available bytes of memory Memory in use Total bytes of memory Number of active worksheets Cell currently Position

Operating system Recalculation mode Excel version Name of system. (PC or Mac)

Information fu

error condition
TRUE

ormula evaluates to an error; otherwise, returns the result of the formula

a list of numbers
Ranking Position High to Low 1 2 3 4 5 6

urrent operating environment.

Windows (32-bit) NT 5.01 Automatic 12 pcdos

Information function

Error in calculation 4.583333333

3 4

=INFO("directory") =INFO("memavail") =INFO("memused") =INFO("totmem") =INFO("numfile") =INFO("origin")

=INFO("osversion") =INFO("recalc") =INFO("release") =INFO("system")

AND
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments
EXAMPLE FRUITS NAME MANGO ORANGE BANANA FORMULA TRUE FALSE RATE 20 50 8 UNITS 1 5 10

OR
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
EXAMPLE FRUITS NAME MANGO ORANGE BANANA FORMULA TRUE FALSE RATE 20 50 8 UNITS 1 5 10

IF
EXAMPLE Name Alan Bob Carol Sales 1000 6000 2000 Target 5000 5000 4000

The IF function returns one value if a condition you specify evaluates to TRUE, and another valu

NOT
EXAMPLE Formula TRUE FALSE Description (Result) Reverses FALSE (TRUE) Reverses an equation that evaluates to TRUE (FALSE)

Reverses the value of its argument. Use NOT when you want to make sure a value is not equa

LOGICAL FUNCTION

FALSE if one or more arguments evaluate to FALSE.

arguments are FALSE.

uates to TRUE, and another value if that condition evaluates to FALSE.

NOT ACHIEVED

to make sure a value is not equal to one particular value.

Depreciati SLN - Straight Line Method of Depreciation Fixed Deprecation Amount deducted every year.

DB Diminishing balance Method of Depreciation Depreciation amount calculate on last year balance of Assets

SLN DB comparison
Asset: Original Cost: Life (years): Salvage Value: Office Furniture 10,000 10 1,000
YEAR

Depreciation
Year SLN DB

1 2 3 4 5 6 7 8 9 10

Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00 Rs.900.00

Rs.2,060.00 Rs.1,635.64 Rs.1,298.70 Rs.1,031.17 Rs.818.75 Rs.650.08 Rs.516.17 Rs.409.84 Rs.325.41 Rs.258.38

1 2 3 4 5 6 7 8 9 10

Depreciation

Salvage is the value at the end of the depreciation(SECO

of Assets

PURCHASE PRICE 5000 SALVAGE 200 LIFE 5 YEAR 1 2 3 4 5 DEP $2,375.00 $1,246.88 $1,246.88 $654.61 $654.61 $343.67 $180.43

SLN

DB

$900.00 $900.00 $900.00 $900.00 $900.00 $900.00 $900.00 $900.00 $900.00 $900.00

$2,060.00 $1,635.64 $1,298.70 $1,031.17 $818.75 $650.08 $516.17 $409.84 $325.41 $258.38

$9,000.00 $9,004.12

e depreciation(SECOND HAND VALUE)

Datebas
DMAX
SL.No.
1 2 3 4 5 6 7

Returns the largest number in a field (column) of records in a list or database that matches con
Product
BLUE TOOTH MOBILE BLUE TOOTH MOBILE BLUE TOOTH DATA CABLE BLUE TOOTH

Company
NOKIA NOKIA LG LG NOKIA MOTOROLA MOTOROLA

Model No.
6300 5200 KG800 KG900 6030 MOTO566 MOTO747

DAVERAGE

Averages the values in a field (column) of records in a list or database that match conditions yo
roll no 1 2 3 4 5 6 english 89 85 74 78 89 85 hindi 85 74 78 89 85 74

DCOUNTA
In Excel, the DCountA function returns the number of cells in a column or database that contains nonblank values and meets

ORDER 10521 10522

QUANTITY 10 45

PAID AMT 7800 8500

10523 10524

85 89

8900 7400

DSUM
Adds the numbers in a field (column) of records in a list or database that match conditions that
ORDER 10521 10522 10523 10524 QUANTITY 10 45 85 89 PAID AMT 7800 8500 8900 7400

ABC
Number 10 -4 -8 5 4

Returns the absolute value of a number. The absolute value of a number is the number without
-39 -86 56 96 39 86 56 96

formula
DGET
ORDER 10521 10522 10523 10524 QUANTITY

Extracts a single value from a column of a list or database that matches conditions that you spe
PAID AMT 10 45 85 89 7800 8500 8900 7400

ORDER
Order

QUANTITY PAID AMT 10521 10 7800

Datebase Function
Sales Price
7200 6300 1050 5000 1600 1200 1500

st or database that matches conditions you that specify.

company

nokia

7200 1500

company

LG company MOTORO 1500 LA LG

atabase that match conditions you specify.

roll no >4
roll no >2

87 81.5

that contains nonblank values and meets a given criteria.

ORDER >10522

ORDER >10521 3

abase that match conditions that you specify.

ORDER >10522
ORDER >10523

16300
7400

a number is the number without its sign.

matches conditions that you specify.

Date Function
NOW

In Excel, the Now function returns the current system date and time. This function will

2/10/2013 3:03
TODAY

In Excel, the Today function returns the current system date. This function will refresh

2/10/2013
Date Value

In Excel, the DateValue function returns the serial number of a date.

38112
NETWORKDAYS
10/1/2008 3/1/2009 11/26/2008 12/4/2008 1/21/2009 Start date of project End date of project Holiday Holiday Holiday

105

Date Function

This function will refresh the date/time value whenever the worksheet recalculates.

ction will refresh the date whenever the worksheet recalculates.

ecalculates.

LOOKUP In Excel, the Lookup function returns a value from a range (one row or one column) or from an arra
FACTORY NO 52 56 85 89 FACTORY CODE 7474 8585 9696 8989

VLOOKUP In Excel, the VLookup function searches for value in the left-most column of table_array and retur
NAME RAJ KUNAL PRAVIN AMIT RAJESH GANESH NEELAM KOMAL PRACHI BRANCH ANDHERI PALNPUR JALGAON DOMBIVALI THANE VASHI PANVEL GUJRAT

BRANCH CODE

HLOOKUP In Excel, the HLookup function searches for value in the top row of table_array and returns the val
NAME RAJ KUNAL PRAVIN AMIT RAJESH GANESH NEELAM KOMAL PRACHI BRANCH ANDHERI PALNPUR JALGAON DOMBIVALI THANE VASHI PANVEL GUJRAT

AMIT
MATCH

In Excel, the Match function searches for a value in an array and returns the relative position

Order ID 1022 1023 1024 1025 1026 1027

Ouantity 5 8 2 4 5 845

4
CONVERT

#N/A

Converts a number from one measurement system to another

Amount To Convert 1 1 1

Converting From in ft yd

Abbreviation for Convert Functions

Lookup Function

e row or one column) or from an array. There are 2 different syntaxes for the Lookup function:

8585

most column of table_array and returns the value in the same row based on the index_number .
BRANCH CODE 78 89 85 74 78 89 85 74 TOTAL SALES 4 5 8 9 7 5 4 7

ow of table_array and returns the value in the same column based on the index_number .
BRANCH CODE 78 89 85 74 78 89 85 74 TOTAL SALES 4 5 8 9 7 5 4 7

ay and returns the relative position of that item.

Unit Cost 10 25 21 11 14 18

Total Cost 50 200 42 44 70 15210

Converting To cm m m

Converted Amount 2.54

tions

up function:

ex_number .