Excel Formulas Generator

The Power Of Excel Fourmula Generator.

ISNUMBER Function

What Does It Do?

This function examines a cell or calculation to determine whether it is a numeric value.

If the cell or calculation is a numeric value, the result TRUE is shown.

If the cell or calculation is not numeric, or is blank, the result FALSE is shown.

Syntax

=ISNUMBER(CellToTest)

The cell to test can be a cell reference or a calculation.

Formatting

No special formatting is needed.

Example

The following table was used by a personnel department to lookup the salary of an employee.

The employee can be entered as a Name or as a Numeric value.

The =ISNUMBER() function has been used to identify the type of entry made, and then the =IF() decides which VLOOKUP to perform.

Type Employee Name or ID: eric

The Salary is: £12,000

=IF(ISNUMBER(E35),VLOOKUP(E35,C29:E33,3,FALSE),VLOOKUP(E35,D29:E33,2,FALSE))

Percentages

Finding a percentage of a value

Example 1

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

The wages department needs to calculate the increases.

Staff on different grades get different pay rises.

Finding a percentage increase

Example 2

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

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

Staff on different grades get different pay rises.

Finding one value as a percentage of another

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 manager needs to specify what will be required each quarter.

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

By analyzing the past year's spending, the manager hopes to predict what will need to be spent in the next year.

Last year's figures

Last year's Quarters as % of last year's Total

Next year's budget: £150,000

Next year's estimated budget requirements

ABS

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 2 shows the same data but using the =ABS() function to correct the calculations.

ADDRESS

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)

• RowNumber: The normal row number from 1 to 16384.
• ColNumber: From 1 to 256, representing columns A to IV.
• Absolute: Can be 1, 2, 3, or 4. Determines the type of reference.
• A1orR1C1: Either TRUE or FALSE, indicating the style of cell address.
• SheetName: A piece of text to be used as the worksheet name in the reference.

The SheetName does not actually have to exist.

AND

What Does It Do?

This function tests two or more conditions to see if they are all true. It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in conjunction with a function such as =IF().

Syntax

=AND(Test1, Test2)

Note that there can be up to 30 possible tests.

Formatting

When used by itself it will show TRUE or FALSE.

Example 1

The following example shows a list of examination results. The teacher wants to find the pupils who scored above average in all three exams. The =AND() function has been used to test that each score is above the average. The result of TRUE is shown for pupils who have scored above average in all three exams.

Averages

47, 54, 60

AREAS

What Does It Do?

This function tests a range to determine whether it is a single block of data or whether it is a multiple selection. If it is a single block, the result will be 1. If it is a multiple block, the result will be the number of ranges selected. The function is designed to be used in macros.

Syntax

=AREAS(RangeToTest)

Formatting

The result will be shown as a number.

Example

The example at the top of this page shows two ranges colored pink and green. These ranges have been given the name PeopleLists. The =AREAS(PeopleLists) gives a result of 2, indicating that there are two separate selections that form the PeopleLists range.

Note

To name multiple ranges, the Ctrl key must be used. In the above example, the pink range was selected as normal, then the Ctrl key was held down before selecting the green range. When a Range Name is created, it will consider both Pink and Green as being one range.

AVERAGE

What Does It Do?

This function calculates the average from a list of numbers. If the cell is blank or contains text, the cell will not be used in the average calculation. If the cell contains zero (0), the cell will be included in the average calculation.

Syntax

=AVERAGE(Range1, Range2, Range3... through to Range30)

Formatting

No special formatting is needed.

Note

To calculate the average of cells that contain text or blanks, use =SUM() to get the total and then divide by the count of the entries using =COUNTA().

Further Usage

What Does It Do?

This function converts a binary number to decimal. Negative numbers are represented using two's-complement notation.

Syntax

=BIN2DEC(BinaryNumber)

The binary number has a limit of ten characters.

Formatting

No special formatting is needed.

CHOOSE Function Examples

Example

The following table was used to calculate the medals for athletes taking part in a race.

The Time for each athlete is entered.

The =RANK() function calculates the finishing position of each athlete.

The =CHOOSE() then allocates the correct medal.

The =IF() has been used to filter out any positions above 3, as this would cause

the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.

COMBIN Function Examples

Example 1

This example calculates the possible number of pairs of letters available from the four characters ABCD.

The proof!

The four letters: ABCD

Example 2

A decorator is asked to design a colour scheme for a new office.

The decorator is given five colours to work with but can only use three in any scheme.

How many colour schemes can be created?

The colours:

• Red
• Green
• Blue
• Yellow
• Black

CONCATENATE Function Examples

Note: You can achieve the same result by using the & operator.

CONVERT Function Examples

Abbreviations:

Prefixes:

What Does It Do?

This function creates a real date by using three normal numbers typed into separate cells.

Syntax:

=DATE(year, month, day)

Formatting:

The result will normally be displayed in the dd/mm/yy format.

By using the Format - Cells - Number - Date command, the format can be changed.

What Does It Do?

This function compares two values and tests whether they are exactly the same.

If the numbers are the same, the result will be 1, otherwise the result is 0.

It only works with numbers; text values produce a result of #VALUE.

The formatting of the number is not significant, so numbers which appear rounded due to the removal of decimal places will still match correctly with non-rounded values.

Syntax:

=DELTA(FirstNumber, SecondNumber)

Formatting:

No special formatting is needed.

Example:

The following table is used to determine how many pairs of similar numbers are in a list.

The =DELTA() function tests each pair, and then the =SUM() function totals them.

Example 1:

Type a month to look for: Feb

Which row needs to be picked out: 4

The result is: 100

=HLOOKUP(F10,D3:F10,F11,FALSE)

Example 2:

=HLOOKUP(B79,G72:I77,MATCH(C79,F73:F77,0)+1,FALSE)

Example 3:

Unit Cost: =HLOOKUP(C127,E111:G112,2,FALSE)

Discount: =HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE)

IF

What Does It Do?

This function tests a condition.

If the condition is met, it is considered to be TRUE.

If the condition is not met, it is considered as FALSE.

Depending upon the result, one of two actions will be carried out.

Syntax

=IF(Condition, ActionIfTrue, ActionIfFalse)

The Condition is usually a test of two cells, such as A1=A2.

The ActionIfTrue and ActionIfFalse can be numbers, text, or calculations.

Formatting

No special formatting is required.

Example 1

The following table shows the Sales figures and Targets for sales reps.

Each has their own target which they must reach.

The =IF() function is used to compare the Sales with the Target.

If the Sales are greater than or equal to the Target, the result of "Achieved" is shown.

If the Sales do not reach the target, the result of "Not Achieved" is shown.

Note that the text used in the =IF() function needs to be placed in double quotes, like "Achieved".

Example 2

The following table is similar to Example 1.

This time the Commission to be paid to the sales rep is calculated.

If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.

If the Sales do not reach the Target, the Commission is only 5% of Sales.

Example 3

This example uses the =AND() function within the =IF() function.

A builders merchant gives a 10% discount on certain product lines.

The discount is only given on products that are on Special Offer when the Order Value is £1000 or above.

The =AND() function is used with the =IF() function to check that the product is on offer and that the value of the order is above £1000.

=IF(AND(C61="Yes", D61>=1000), D61*10%, 0)

INDEX

Holiday booking price list.

What Does It Do?

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns.

It can be used with a single block of data or non-continuous blocks.

Syntax

Syntax 1

=INDEX(RangeToLookIn, Coordinate)

This is used when the RangeToLookIn is either a single column or row.

The Coordinate indicates how far down or across to look when picking the data from the range.

Example:

Colours
Red
Green
Blue

Type either 1, 2 or 3: 2
The colour is: Green
=INDEX(D32:D34,D36)

Syntax 2

=INDEX(RangeToLookIn, RowCoordinate, ColumnColumnCordinate)

This syntax is used when the range is made up of rows and columns.

Example:

Country Currency Population Capitol
England Sterling 50 M London
France Franc 40 M Paris
Germany DM 60 M Bonn
Spain Peseta 30 M Barcelona

Type 1, 2, 3, or 4 for the country: 2
Type 1, 2, or 3 for statistics: 3
The result is: Paris
=INDEX(D45:F48,F50,F51)

Syntax 3

=INDEX(NamedRangeToLookIn, RowCoordinate, ColumnColumnCordinate, AreaToPickFrom)

Using this syntax, the range to look in can be made up of multiple areas.

The easiest way to refer to these areas is to select them and give them a single name.

The AreaToPickFrom indicates which of the multiple areas should be used.

Example:

NORTH Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,000 £2,000 £3,000 £4,000
Wood £5,000 £6,000 £7,000 £8,000
Glass £9,000 £10,000 £11,000 £12,000

SOUTH Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500

Type 1, 2, or 3 for the product: 1
Type 1, 2, 3, or 4 for the Qtr: 3
Type 1 for North or 2 for South: 2
The result is: 3500
=INDEX(NorthAndSouth,F76,F77,F78)

Example:

This is an extended version of the previous example.

It allows the names of products and the quarters to be entered.

The =MATCH() function is used to find the row and column positions of the names entered.

These positions are then used by the =INDEX() function to look for the data.

EAST Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,000 £2,000 £3,000 £4,000
Wood £5,000 £6,000 £7,000 £8,000
Glass £9,000 £10,000 £11,000 £12,000

WEST Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500

Type 1, 2, or 3 for the product: wood
Type 1, 2, 3, or 4 for the Qtr: qtr2
Type 1 for North or 2 for South: west

The result is: 6500

=INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))

LCM

What Does It Do?

This function calculates the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers.

Syntax:

=LCM(Number1, Number2, Number3... through to Number29)

Formatting:

No special formatting is needed.

LOOKUP (Array)

Type a Name in this cell: Eric

The March value for this person is: 77 =LOOKUP(F12, D4:G10)

What Does It Do?

This function looks for a piece of information in a list and then picks an item from the last cell in the adjacent row or column. It selects the data from the end of the row or column, making it unsuitable for picking data from partway across a list (use VLOOKUP or HLOOKUP instead).

Syntax:

=LOOKUP(WhatToLookFor, RangeToLookIn)

Formatting:

No special formatting is needed.

Problems:

The list of information to be looked through must be sorted in ascending order; otherwise, errors will occur, resulting in either #N/A or incorrect results.

Table 1 shows the Name column sorted alphabetically, and using =LOOKUP() will yield correct results.

Table 2 shows the same data but not sorted. Sometimes the results will be correct, but other times the result will be an #N/A error or an incorrect figure.

Name: Eric

Value: 77

=LOOKUP(C88, B80:E86)

=LOOKUP(H88, G80:J86)

MATCH

Names Values

Bob 250

Alan 600

David 1000

Carol 4000

Type a name to look for: Alan Type a value: 1000

The position of Alan is: 2 Value position: 3

=MATCH(E9, E4:E7, 0) =MATCH(I9, I4:I7, 1)

What Does It Do?

This function looks for an item in a list and shows its position.

It can be used with text and numbers.

It can look for an exact match or an approximate match.

Syntax:

=MATCH(WhatToLookFor, WhereToLook, TypeOfMatch)

The TypeOfMatch can be either 0, 1, or -1.

Using 0

This option looks for an exact match. If no match is found, the #NA error is shown.

Examples:

=MATCH(G45, G40:G43, 0)

Using 1

This option looks for an exact match or the next lowest number if no exact match exists.

If there is no match or next lowest number, the #NA error is shown.

Examples:

=MATCH(G62, G57:G60, 1)

Using -1

This option looks for an exact match or the next highest number if no exact match exists.

If there is no exact match or next highest number, the #NA error is shown.

Examples:

=MATCH(G79, G74:G77, -1)

Example 4

The tables below were used by a bus company taking bookings for bus tours.

They needed to allocate a bus with enough seats for all the passengers.

The list of bus sizes was entered in a list.

The number of passengers on the tour was then entered.

The =MATCH() function looks down the list to find the bus with enough seats.

If the number of passengers is not an exact match, the next biggest bus is picked.

After the =MATCH() function has found the bus, the =INDEX() function is used to look down the list again and pick out the actual bus size required.

Bus Size

Passengers on the tour: 23

Bus size needed: 50

=INDEX(D95:D99, MATCH(H94, D95:D99, -1), 0)

Example 5

The tables below were used by a school to calculate the exam grades for pupils.

The list of grade breakpoints was entered in a list.

The pupils' scores were entered in another list.

The pupils' scores are compared against the breakpoints.

If an exact match is not found, the next lowest breakpoint is used.

The =INDEX() function then looks down the Grade list to find the grade.

=INDEX(D111:D114, MATCH(G114, C111:C114, 1), 0)

• Click Here for More. ..➤