SOME EXCEL FORMULAS

Split Forename and Surname

The following formula are useful when you have one cell containing text which needs

to be split up.

One of the most common examples of this is when a persons Forename and Surname

are entered in full into a cell.

The formula use various text functions to accomplish the task.

Each of the techniques uses the space between the names to identify where to split.

Finding the First Name

Full Name

First Name

Alan Jones

Alan

 =LEFT(C14,FIND(" ",C14,1))

Bob Smith

Bob

 =LEFT(C15,FIND(" ",C15,1))

Carol Williams

Carol

 =LEFT(C16,FIND(" ",C16,1))

Finding the Last Name

Full Name

Last Name

Alan Jones

Jones

 =RIGHT(C22,LEN(C22)-FIND(" ",C22))

Bob Smith

Smith

 =RIGHT(C23,LEN(C23)-FIND(" ",C23))

Carol Williams

Williams

 =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names.

If there is also a middle name, the last name formula will be incorrect.

To solve the problem you have to use a much longer calculation.

Full Name

Last Name

Alan David Jones

Jones

Bob John Smith

Smith

Carol Susan Williams

Williams

 =RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name

Full Name

Middle Name

Alan David Jones

David

Bob John Smith

John

Carol Susan Williams

Susan

 =LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

Percentages

There are no specific functions for calculating percentages.

You have to use the skills you were taught in your maths class at school!

Finding a percentage of a value

Initial value

120

% to find

25%

Percentage value

30

 =D8*D9

Example 1

A company is about to give its staff a pay rise.

The wages department need to calculate the increases.

Staff on different grades get different pay rises.

Grade

% Rise

A

10%

B

15%

C

20%

Name

Grade

Old Salary

Increase

Alan

A

10,000

1,000

 =E23*LOOKUP(D23,$C$18:$C$20,$D$18:$D$20)

Bob

B

20,000

3,000

 =E24*LOOKUP(D24,$C$18:$C$20,$D$18:$D$20)

Carol

C

30,000

6,000

 =E25*LOOKUP(D25,$C$18:$C$20,$D$18:$D$20)

David

B

25,000

3,750

 =E26*LOOKUP(D26,$C$18:$C$20,$D$18:$D$20)

Elaine

C

32,000

6,400

 =E27*LOOKUP(D27,$C$18:$C$20,$D$18:$D$20)

Frank

A

12,000

1,200

 =E28*LOOKUP(D28,$C$18:$C$20,$D$18:$D$20)

Finding a percentage increase

Initial value

120

% increase

25%

Increased value

150

 =D33*D34+D33

Example 2

A company is about to give its staff a pay rise.

The wages department need to calculate the new salary including the % increase.

Staff on different grades get different pay rises.

Grade

% Rise

A

10%

B

15%

C

20%

Name

Grade

Old Salary

Increase

Alan

A

 £      10,000

11,000

 =E48*LOOKUP(D48,$C$18:$C$20,$D$18:$D$20)+E48

Bob

B

 £      20,000

23,000

 =E49*LOOKUP(D49,$C$18:$C$20,$D$18:$D$20)+E49

Carol

C

 £      30,000

36,000

 =E50*LOOKUP(D50,$C$18:$C$20,$D$18:$D$20)+E50

David

B

 £      25,000

28,750

 =E51*LOOKUP(D51,$C$18:$C$20,$D$18:$D$20)+E51

Elaine

C

 £      32,000

38,400

 =E52*LOOKUP(D52,$C$18:$C$20,$D$18:$D$20)+E52

Frank

A

 £      12,000

13,200

 =E53*LOOKUP(D53,$C$18:$C$20,$D$18:$D$20)+E53

Finding one value as percentage of another

Value A

120

Value B

60

A as % of B

50%

 =D59/D58

You will need to format the result as % by using the % button

on the toolbar.

Example 3

A manager has been asked to submit budget requirements for next year.

The manger needs to specify what will be required each quarter.

The manager knows what has been spent by each region in the previous year.

By analysing the past years spending, the manager hopes to predict

what will need to be spent in the next year.

Last years figures

Region

Q1

Q2

Q3

Q4

North

     9,000

           2,000

         9,000

        7,000

South

     7,000

           4,000

         9,000

        5,000

East

     2,000

           8,000

         7,000

        3,000

West

     8,000

           9,000

         6,000

        5,000

Total

Total

    26,000

         23,000

       31,000

      20,000

  100,000

Last years Quarters as % of last years Total

Region

Q1

Q2

Q3

Q4

North

9%

2%

9%

7%

 =G74/$H$78

South

7%

4%

9%

5%

 =G75/$H$78

East

2%

8%

7%

3%

 =G76/$H$78

West

8%

9%

6%

5%

 =G77/$H$78

Total

26%

23%

31%

20%

 =G78/$H$78

Next years budget

       150,000

Next years estimated budget requirements

Region

Q1

Q2

Q3

Q4

North

    13,500

           3,000

       13,500

      10,500

 =G82*$E$88

South

    10,500

           6,000

       13,500

        7,500

 =G83*$E$88

East

     3,000

         12,000

       10,500

        4,500

 =G84*$E$88

West

    12,000

         13,500

         9,000

        7,500

Total

Total

    39,000

         34,500

       46,500

      30,000

  150,000

Finding an original value after an increase has been applied

Increased value

150

% increase

25%

Original value

120

 =D100/(100%+D101)

Example 4

An employ has to submit an expenses claim for travelling and accommodation.

The claim needs to show the VAT tax portion of each receipt.

Unfortunately the receipts held by the employee only show the total amount.

The employee needs to split this total to show the original value and the VAT amount.

VAT rate

17.50%

Receipt

Total

Actual Value

Vat Value

Petrol

     10.00

             8.51

          1.49

 =D113-D113/(100%+$D$110)

Hotel

    235.00

         200.00

         35.00

Petrol

    117.50

         100.00

         17.50

 =D115/(100%+$D$110)

SUM using names

You can use the names typed at the top of columns or side of rows in calculations

simply by typing the name into the formula.

Try this example:

Go to cell C16 and then enter the formula =SUM(jan)

The result will show.

This formula can be copied to D16 and E16, and the names change to Feb and Mar.

Jan

Feb

Mar

North

45

50

50

South

30

25

35

East

35

10

50

West

20

50

5

Total

If it does not work !

The feature may have been switched off on your computer.

You can switch it on by using Tools, Options, Calculation, Accept Labels in Formula.

Age Calculation

You can calculate a persons age based on their birthday and todays date.

The calculation uses the DATEDIF() function.

The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

(Makes you wonder what else Microsoft forgot to tell us!)

Birth date :

19/06/1982

Years lived :

28

 =DATEDIF(C8,TODAY(),"y")

and the months :

2

 =DATEDIF(C8,TODAY(),"ym")

and the days :

20

 =DATEDIF(C8,TODAY(),"md")

You can put this all together in one calculation, which creates a text version.

Age is 28 Years, 2 Months and 20 Days

 ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age

This method gives you an age which may potentially have decimal places representing the months.

If the age is 20.5, the .5 represents 6 months.

Birth date :

01-Jan-60

Age is :

50.69

 =(TODAY()-C23)/365.25

AutoSum Shortcut Key

Instead of using the AutoSum button from the toolbar,

you can press Alt and = to achieve the same result.

Try it here :

Move to a blank cell in the Total row or column, then press Alt and =.

or

Select a row, column or all cells and then press Alt and =.

Jan

Feb

Mar

Total

North

10

50

90

South

20

60

100

East

30

70

200

West

40

80

300

Total

ABS

Number

Absolute Value

10

10

=ABS(C4)

-10

10

=ABS(C5)

1.25

1.25

=ABS(C6)

-1.25

1.25

=ABS(C7)

What Does it Do ?

This function calculates the value of a number, irrespective of whether it is positive or negative.

Syntax

 =ABS(CellAddress or Number)

Formatting

The result will be shown as a number, no special formatting is needed.

Example

The following table was used by a company testing a machine which cuts timber.

The machine needs to cut timber to an exact length.

Three pieces of timber were cut and then measured.

In calculating the difference between the Required Length and the Actual Length it does

not matter if the wood was cut too long or short, the measurement needs to be expressed as

an absolute value.

Table 1 shows the original calculations.

The Difference for Test 3 is shown as negative, which has a knock on effect

when the Error Percentage is calculated.

Whether the wood was too long or short, the percentage should still be expressed

as an absolute value.

Table 1

Test
Cut

Required
Length

Actual
Length

Difference

Error
Percentage

Test 1

120

120

0

0%

Test 2

120

90

30

25%

Test 3

120

150

-30

-25%

=D36-E36

Table 2 shows the same data but using the =ABS() function to correct the calculations.

Table 2

Test
Cut

Required
Length

Actual
Length

Difference

Error
Percentage

Test 1

120

120

0

0%

Test 2

120

90

30

25%

Test 3

120

150

30

25%

=ABS(D45-E45)

ADDRESS

Type a column number :

2

Type a row number :

3

Type a sheet name :

Hello

$B$3

 =ADDRESS(F4,F3,1,TRUE)

B$3

 =ADDRESS(F4,F3,2,TRUE)

$B3

 =ADDRESS(F4,F3,3,TRUE)

B3

 =ADDRESS(F4,F3,4,TRUE)

R3C2

 =ADDRESS(F4,F3,1,FALSE)

R3C[2]

 =ADDRESS(F4,F3,2,FALSE)

R[3]C2

 =ADDRESS(F4,F3,3,FALSE)

R[3]C[2]

 =ADDRESS(F4,F3,4,FALSE)

Hello!$B$3

 =ADDRESS(F4,F3,1,TRUE,F5)

Hello!B$3

 =ADDRESS(F4,F3,2,TRUE,F5)

Hello!$B3

 =ADDRESS(F4,F3,3,TRUE,F5)

Hello!B3

 =ADDRESS(F4,F3,4,TRUE,F5)

What Does It Do ?

This function creates a cell reference as a piece of text, based on a row and column

numbers given by the user.

This type of function is used in macros rather than on the actual worksheet.

Syntax

=ADDRESS(RowNumber,ColNumber,Absolute,A1orR1C1,SheetName)

The RowNumber is the normal row number from 1 to 16384.

The ColNumber is from 1 to 256, cols A to IV.

The Absolute can be 1,2,3 or 4.

   When 1 the reference will be in the form $A$1, column and row absolute.

   When 2 the reference will be in the form A$1, only the row absolute.

   When 3 the reference will be in the form $A1, only the column absolute.

   When 4 the reference will be in the form A1, neither col or row absolute.

The A1orR1C1 is either TRUE of FALSE.

   When TRUE the reference will be in the form A1, the normal style for cell addresses.

   When FALSE the reference will be in the form R1C1, the alternative style of cell address.

The SheetName is a piece of text to be used as the worksheet name in the reference.

   The SheetName does not actually have to exist.