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

=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

10%

15%

20%

Name

Grade

Old Salary

Increase

Alan

10,000

1,000

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

Bob

20,000

3,000

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

Carol

30,000

6,000

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

David

25,000

3,750

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

Elaine

32,000

6,400

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

Frank

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

10%

15%

20%

Name

Grade

Old Salary

Increase

Alan

£ 10,000

11,000

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

Bob

£ 20,000

23,000

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

Carol

£ 30,000

36,000

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

David

£ 25,000

28,750

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

Elaine

£ 32,000

38,400

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

Frank

£ 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

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

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

26,000

23,000

31,000

20,000

100,000

Last years Quarters as % of last years Total

Region

North

=G74/$H$78

South

=G75/$H$78

East

=G76/$H$78

West

=G77/$H$78

Total

26%

23%

31%

20%

=G78/$H$78

Next years budget

150,000

Next years estimated budget requirements

Region

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

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

South

East

West

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.

Instant Charts

You can create a chart quickly without having to use the chart button on

the toolbar by pressing the function key F11 while inside a range of data.

Jan

Feb

Mar

North

South

East

West

Click anywhere inside the table above.

Then press F11.

Brackets in formula

Sometimes you will need to use brackets, (also known as 'braces'), in formula.

This is to ensure that the calculations are performed in the order that you need.

The need for brackets occurs when you mix plus or minus with divide or multiply.

Mathematically speaking the * and / are more important than + and - .

The * and / operations will be calculated before + and - .

Example 1 : The wrong answer !

=C12+C13*C14

You may expect that 10 + 20 would equal 30

And then 30 * 2 would equal 60

But because the * is calculated first Excel sees the

calculation as 20 * 2 resulting in 40

And then 10 + 40 resulting in 50

Example 2 : The correct answer.

=(C27+C28)*C29

By placing brackets around (10+20) Excel performs this

part of the calulation first, resulting in 30

Then the 30 is multipled by 2 resulting in 60

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 :

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

and the months :

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

and the days :

=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 =.

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

Jan

Feb

Mar

Total

North

South

100

East

200

West

300

Total

ABS

Number

Absolute Value

=ABS(C4)

-10

=ABS(C5)

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

Test 2

120

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

Test 2

120

25%

Test 3

120

150

25%

=ABS(D45-E45)

ADDRESS

Type a column number :

Type a row number :

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)

=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.

CEILING

Number

Raised Up

2.1

=CEILING(C4,1)

1.5

=CEILING(C5,1)

1.9

=CEILING(C6,1)

=CEILING(C7,30)

=CEILING(C8,30)

=CEILING(C9,30)

What Does It Do ?

This function rounds a number up to the nearest multiple specified by the user.

Syntax

=CEILING(ValueToRound,MultipleToRoundUpTo)

The ValueToRound can be a cell address or a calculation.

Formatting

No special formatting is needed.

Example 1

The following table was used by a estate agent renting holiday apartments.

The properties being rented are only available on a weekly basis.

When the customer supplies the number of days required in the property the =CEILING()

function rounds it up by a multiple of 7 to calculate the number of full weeks to be billed.

Days Required

Days To
Be Billed

Customer 1

=CEILING(D28,7)

Customer 2

=CEILING(D29,7)

Customer 3

=CEILING(D30,7)

Example 2

The following table was used by a builders merchant delivering products to a construction site.

The merchant needs to hire trucks to move each product.

Each product needs a particular type of truck of a fixed capacity.

Table 1 calculates the number of trucks required by dividing the Units To Be Moved by

the Capacity of the truck.

This results of the division are not whole numbers, and the builder cannot hire just part

of a truck.

Table 1

Item

Units To
Be Moved

Truck
Capacity

Trucks
Needed

Bricks

1000

300

3.33

=D45/E45

Wood

5000

600

8.33

=D46/E46

Cement

2000

350

5.71

=D47/E47

Table 2 shows how the =CEILING() function has been used to round up the result of

the division to a whole number, and thus given the exact amount of trucks needed.

Table 2

Item

Units To
Be Moved

Truck
Capacity

Trucks
Needed

Bricks

1000

300

=CEILING(D54/E54,1)

Wood

5000

600

=CEILING(D55/E55,1)

Cement

2000

350

=CEILING(D56/E56,1)

Example 3

The following tables were used by a shopkeeper to calculate the selling price of an item.

The shopkeeper buys products by the box.

The cost of the item is calculated by dividing the Box Cost by the Box Quantity.

The shopkeeper always wants the price to end in 99 pence.

Table 1 shows how just a normal division results in varying Item Costs.

Table 1

Item

Box Qnty

Box Cost

Cost Per Item

Plugs

£20

1.81818

=D69/C69

Sockets

£18.25

2.60714

=D70/C70

Junctions

£28.10

5.62000

=D71/C71

Adapters

£28

1.75000

=D72/C72

Table 2 shows how the =CEILING() function has been used to raise the Item Cost to

always end in 99 pence.

Table 2

Item

In Box

Box Cost

Cost Per Item

Raised Cost

Plugs

£20

1.81818

1.99

Sockets

£18.25

2.60714

2.99

Junctions

£28.10

5.62000

5.99

Adapters

£28

1.75000

1.99

=INT(E83)+CEILING(MOD(E83,1),0.99)

Explanation

=INT(E83)

Calculates the integer part of the price.

=MOD(E83,1)

Calculates the decimal part of the price.

=CEILING(MOD(E83),0.99)

Raises the decimal to 0.99

Menu

CA Sanat Pyne

CA Navin Jain

CA Sanat Pyne

CA Sanat Pyne

CA Navin Jain

Leave a Reply

Recent Threads

Related Threads

Menu

Forum Search

SOME EXCEL FORMULAS

CA Sanat Pyne

CA Navin Jain

CA Sanat Pyne

CA Sanat Pyne

CA Navin Jain

Leave a Reply

Recent Threads

Related Threads