UPPER (Converts text to uppercase)

What Does It Do?

This function converts all characters in a piece of text to upper case.

Syntax

=UPPER(TextToConvert)

Formatting

No special formatting is needed.

Example-1:

See the example for FREQUENCY.

VALUE (Estimates variance based on a sample)

What Does It Do?

This function converts a piece of text which resembles a number into an actual value.

If the number in the middle of a long piece of text it will have to be extracted using other text functions such as =SEARCH(), =MID(), =FIND(), =SUBSTITUTE, =LEFT() or =RIGHT().

Syntax

=VALUE(TextToConvert)

Formatting

No special formatting is needed.

The result will be shown as a value, based upon the original text.

If the £ sign is included in the text it will be ignored.

If the % sign is included in the text, the result will be a decimal fraction which can then be formatted as a percentage.

If the original text format appears as a time hh:mm the result will be a time.

The same will be true for other recognised formats.

Explanation of formula shown above

To extract the values from the following text is complicated!

The actual percentage value is of variable length, it can be either one, two or three digits long.

The only way to identify the value is the fact it always ends with the % sign.

There is no way to identify the beginning of the value, other than it is preceded by a space.

The main problem is calculating the length of the value to extract.

If the extraction assumes the maximum length of three digits and the % sign, errors will occur when the percentage is only one digit long, as alphabetic characters will be included.

To get around the problem the =SUBSTITUTE() function was used to increase the size of the spaces in the text.

Now when the extraction takes place any unnecessary characters will be spaces which are ignored by the =VALUE() function.

VAR (Estimates variance based on a sample for Statistical Purpose)

What Does It Do?

This function calculates the sample population variance of a list of values.

A sample population is used when the list of values represents a sample of a population.

Syntax

=VAR(Range1,Range2,Range3 through to Range30)

Formatting

No special formatting is needed.

Example:

The table below was used by a company interested in buying a new machine to pack washing powder.

Three machines were short listed and allow to run for a day.

At the end of the day four boxes of soap powder were picked at random from the production of each machine.

The boxes were weighed and the =VAR() function used as these boxes only represented a sample of the complete days production.

The machine with the smallest variance was the most consistent.

Explanation of formula:

This finds the lowest value.                                =MIN(H4:H6)

This finds the position of the lowest value.         =MATCH(MIN(H4:H6),H4:H6,0)

This looks down the Machine column to             =INDEX(C4:C6,MATCH(MIN(H4:H6),H4:H6,0))

find the machine name.

VARP (Calculates variance based on the entire population for Statistical Purpose)

What Does It Do?

This function calculates the variance of a list of values.

The variance is calculated on the basis that the values represent the entire population.

Syntax

=VARP(Range1,Range2,Range3 through to Range30)

Formatting

No special formatting is needed.

Example:

The table below was used by a company interested in buying a new machine to pack washing powder.

A trial run a just four boxes per machine were produced.

The boxes were weighed and the =VARP() function used as these boxes represented the entire test run.

The machine with the smallest variance was the most consistent.

Explanation of formula:

This finds the lowest value.                                =(MIN(H4:H6))

This finds the position of the lowest value.         =MATCH(MIN(H4:H6),H4:H6,0)

This looks down the Machine column to             =INDEX(C4:C6,MATCH(MIN(H4:H6),H4:H6,0))

find the machine name.

VLOOKUP (Looks in the first column of an array and moves across the row to return the value of a cell)

What Does It Do?

This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.

Syntax

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

The ItemToFind is a single item specified by the user.

The RangeToLookIn is the range of data with the row headings at the left hand side.

The ColumnToPickFrom is how far across the table the function should look to pick from.

The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting

No special formatting is needed.

Example-1:

This table is used to find a value based on a specified name and month.

The =VLOOKUP() is used to scan down to find the name.

The problem arises when we need to scan across to find the month column.

To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.

Example-2:

This example shows how the =VLOOKUP() is used to pick the cost of a spare part for different makes of cars.

The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.

When the make is found, the =VLOOKUP() then scans across to find the price, using the result of the =MATCH() function to find the position of the make of car.

The functions use the absolute ranges indicated by the dollar symbol . This ensures that when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do not change.

WEEKDAY (Converts a serial number to a day of the week)

What Does It Do?

This function shows the day of the week from a date.

Syntax

=WEEKDAY(Date,Type)

Type : This is used to indicate the week day numbering system.

1 : will set Sunday as 1 through to Saturday as 7

2 : will set Monday as 1 through to Sunday as 7.

3 : will set Monday as 0 through to Sunday as 6.

If no number is specified, Excel will use 1.

Formatting

The result will be shown as a normal number.

To show the result as the name of the day, use Format, Cells, Custom and set the Type to ddd or dddd.

Example:

The following table was used by a hotel which rented a function room.

The hotel charged different rates depending upon which day of the week the booking was for.

The Booking Date is entered.

The Actual Day is calculated.

The Booking Cost is picked from a list of rates using the =LOOKUP() function.

Workday (Returns the serial number of the date before or after a specified number of workdays)

What Does It Do?

Use this function to calculate a past or future date based on a starting date and a specified number of days. The function excludes weekends and holidays and can therefore be used to calculate delivery dates or invoice dates.

Syntax

=WORKDAY(StartDate,Days,Holidays)

Formatting

The result will normally be shown as a number which can be formatted to a normal date by using Format,Cells,Number,Date.

Example:

The following example shows how the function can be used to calculate delivery dates based upon an initial Order Date and estimated Delivery Days.

Year (Converts a serial number to a year)

What Does It Do?

This function extracts the year number from a date.

Syntax

=YEAR(Date)

Formatting

The result is shown as a number.

Year Fraction (Returns the year fraction representing the number of whole days between start_date and end_date)

What does it to?
This function calculates the difference between two dates and expresses the result
as a decimal fraction.
Syntax
=YEARFRAC(StartDate,EndData,Basis)
Basis : Defines the calendar system to be used in the function.
0 : or omitted USA style 30 days per month divided by 360.
1 : 29 or 30 or 31 days per month divided by 365.
2 : 29 or 30 or 31 days per month divided by 360.
3 : 29 or 30 0r 31 days per month divided by 365.
4 : European 29 or 30 or 31 days divided by 360.
Formatting
The result will be shown as a decimal fraction, but can be formatted as a percent.
Example:
The following table was used by a company which hired people on short term contracts for a part of the year.
The Pro Rata Salary which represents the annual salary is entered.
The Start and End dates of the contract are entered.
The =YEARFRAC() function is used to calculate Actual Salary for the portion of the year.

Note:
The extra 1 has been added to the End date to compensate for the fact that the =YEARFRAC()
function calculates from the Start date up to, but not including, the End date.

Project Dates & House Building (For using date calculation.)

Excel can work with time very easily.
Time can be entered in various different formats and calculations performed.
There are one or two oddities, but nothing which should put you off working with it.
See the TimeSheet example for an example.

Typing time:
When time is entered into worksheet it should be entered with a colon between
the hour and the minutes, such as 12:30, rather than 12.30