COLUMNS USED FOR RESULT

FORMULA

H

I

J

RESULT

REFERENCES

File Path:

=CELL ( "filename" )

E:\Tip Sheets\Excel\[Excel - Tips.xls]Excel (Formulas 1)

DATES

Today's Date:

=TODAY ( )

10/15/04

Today's Day:

=DAY(TODAY ( ) )

15

Today's Month:

=MONTH ( TODAY ( ) )

10

Today's Year:

=YEAR ( TODAY ( ) )

2003

FORMATTING - CAPITALIZATION

Upper Case:

=UPPER ( H26 )

all caps

ALL CAPS

Lower Case:

=LOWER ( H28 )

LOWER CASE

lower case

Capitalize First Letter of Each Word:

=PROPER ( H30 )

typical capitalization

Typical Capitalization

COMBINING / EXTRACTING DATA IN CELLS

Concatenate:

=CONCATENATE ( H35, I35, J35 )

One

Two

Three

OneTwoThree

Concatenate + Additional Data:

=CONCATENATE ( H35, " + ", I35, " = ", J35 )

One

Two

Three

One + Two = Three

Extract Left Portion of Data:

=LEFT ( H39, 4)

CutsLeftFourCharacters

Cuts

Extract Right Portion of Data:

=RIGHT ( H41, 10)

CutsRightTenCharacters

Characters

Extract Middle Portion of Data:

=MID ( H43, 8, 6 )

CutsOutMiddleSection

Middle

Cuts Out Additional Spacing:

=TRIM ( H45 )

Cuts   Out       Extra   Spaces

Cuts Out Extra Spaces

DATA FORMAT

Absolute Value:

=ABS ( H50 )

-125.13

125.13

Round Data:

=ROUND ( H52, 0 )

-125.13

-125

=ROUND ( H52, -2 )

125.13

-100

=ROUND ( H52, 1 )

125.13

-125.1

Replacing Errors:

=IF ( ISERROR (H56 / I56 ), "-" , H56 / I56 )

5

0

-

CONDITIONAL FORMULAS

If / Then:

=IF ( H61=1, "ONE", "TWO")

1

ONE

=IF ( H62=1, "ONE", "TWO")

2

TWO

If / Then with OR:

=IF ( OR ( H64=1, H64=2 ), "ONE or TWO", "FALSE")

1

ONE  or TWO

If / Then with AND:

=IF ( AND (H66=1, I66=2 ), "YES", "NO")

1

2

YES

=IF ( AND (H67=1, I67=2 ), "YES", "NO")

1

1

NO

CHOOSE FROM LIST

Choose:

=CHOOSE ( H72, "ONE", "TWO", "THREE", "FOUR" )

3

THREE

COMPARE TWO CELLS

Compare:

=EXACT ( H77, J77 )

ExactMatch?

ExactMatch?

TRUE

=EXACT ( H78, J78 )

ExactMatch?

exactmatch?

FALSE

FIND LENGTH OF DATA

Number of Characters:

=LEN ( H83 )

123456

6

=LEN ( H84 )

SPACE

5

REPEAT

Repeat Characters:

=REPT ( "-*", 5 )

-*-*-*-*-*

CELL PADDING (IDEAL FOR LOADING CHARACTER LENGTH SENSITIVE DATA)

Padding:

=CONCATENATE ( ( REPT ( 0, ( 8 - LEN ( I94 )))), I94 )

8210

00008210

RANDOM NUMBER GENERATOR

Generates Random Factor Between 0 and 1:

=RAND ()

0.0119677

Generates Random Number Between 1 and 100:

=RANDBETWEEN ( 1, 100 )

#NAME?

TIME VALUE OF MONEY

Net Present Value:

Discount Rate:

5.00%

Yr 1 Return:

$10,000

=NPV( H105, J105:J107)+H106

Initial Inves, t=0:

(40,000)

Yr 2 Return:

$15,000

$    (2,617)

Yr 3 Return:

$16,500

Present Value:

Discount Rate:

5.00%

Years:

10

=PV (H109/12, J109*12, H110)

Monthly Payment:

$500

($47,141)

Future Value:

Interest Rate:

8.00%

# of Payments:

12

=FV ( H112/12, J112, H113 )

Payment:

$500

($6,225)

Payment:

Interest Rate:

3.00%

Years:

28

=PMT( H115/12, J115*12, H116)

Future Value:

$50,000

($220)

Internal Rate of Return:

Initial Cost:

Income Yr 1:

Income Yr 2:

=IRR ( H119:J119 )

-$100,000

$50,000

$58,000

5.16%

Effective Interest Rate:

Nominal Rate:

5.25%

Periods:

6

=EFFECT ( H121, J121 )

#NAME?

STATISTICS

Standard Deviation:

=STDEV ( H127:J127 )

1

2

3

1.000

CoVariance:

=COVAR ( H129:H130, I129:I130 )

1

2

0.500

2

4

Variance (Sample Population):

=VAR ( H132:J132 )

1

2

3

1.000

Variance (Entire Population):

=VARP ( H134:J134 )

1

2

3

0.667

Probability - T-Test:

=TTEST ( H136:H137, I136:I137, 2, 1 )

1

4

0.090

3

7

Probability - F-Test:

=FTEST ( H139:H140, I139:I140 )

1

4

0.749

3

7