| | | | |
|---|
| DAVERAGE | =DAVERAGE(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and produces and average. | Database | PDF File |
| DCOUNT | =DCOUNT(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and counts the values in a specified column. | Database | PDF File |
| DCOUNTA | =DCOUNTA(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and counts the non blank cells in a specified column. | Database | PDF File |
| DGET | =DGET(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and produces one result. | Database | PDF File |
| DMAX | =DMAX(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and produces the largest value from a specified column. | Database | PDF File |
| DMIN | =DMIN(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and produces smallest value from a specified column. | Database | PDF File |
| DSUM | =DSUM(DatabaseRange,FieldName,CriteriaRange) | This function examines a list of information and produces the total. | Database | PDF File |
| DATE | =DATE(year,month,day) | This function creates a real date by using three normal numbers typed into separate cells. | Date | PDF File |
| DATEDIF | =DATEDIF(FirstDate,SecondDate,"Interval") | This function calculates the difference between two dates. | Date | PDF File |
| DATEVALUE | =DATEVALUE(text) | The function is used to convert a piece of text into a date which can be used in calculations. | Date | PDF File |
| EDATE | =EDATE(StartDate,Months) | This function is used to calculate a date which is a specific number of months in the past or in the future. | Date | PDF File |
| MINUTE | =MINUTE(Number) | The function will show the minute of the hour based upon a time or a number. | Date | PDF File |
| MONTH | =MONTH(Date) | This function extracts the month from a complete date. | Date | PDF File |
| NETWORKDAYS | =NETWORKDAYS(StartDate,EndDate,Holidays) | This function will calculate the number of working days between two dates. | Date | PDF File |
| NOW | =NOW() | This function shows the current date and time. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. | Date | PDF File |
| SECOND | =SECOND(Number) | The function will show the second of the minute based upon a time or a number. | Date | PDF File |
| TIME | =TIME(Hour,Minute,Second) | This function will convert three separate numbers to an actual time. | Date | PDF File |
| TIMEVALUE | =TIMEVALUE(Text) | This function will show an actual time based on a piece of text which looks like a time. | Date | PDF File |
| TODAY | =TODAY() | Use this to show the current date. | Date | PDF File |
| WEEKDAY | =WEEKDAY(Date,Type) | This function shows the day of the week from a date. | Date | PDF File |
| WORKDAY | =WORKDAY(StartDate,Days,Holidays) | Use this function to calculate a past or future date based on a starting date and a specified number of days. | Date | PDF File |
| YEAR | =YEAR(Date) | This function extracts the year number from a date. | Date | PDF File |
| YEARFRAC | =YEARFRAC(StartDate,EndData,Basis) | This function calculates the difference between two dates and expresses the result as a decimal fraction. | Date | PDF File |
| CONVERT | =CONVERT(AmountToConvert,UnitToConvertFrom,UnitToConvertTo) | This function converts a value measure in one type of unit, to the same value expressed in a different type of unit, such as Inches to Centimetres. | Engineering | PDF File |
| DELTA | =DELTA(FirstNumber,SecondNumber) | This function compares two values and tests whether they are exactly the same. | Engineering | PDF File |
| GESTEP | =GESTEP(NumberToTest,NumberToTestAgainst) | This function test a number to see if it is greater than or equal to another number. | Engineering | PDF File |
| DB | =DB(PurchasePrice,SalvageValue,Life,PeriodToCalculate,FirstYearMonth) | This function calculates deprecation based upon a fixed percentage. | Financial | PDF File |
| SLN | =SLN(OriginalCost,SellingPrice,LengthOfOwnership) | This function calculates the Straight Line Depreciation of an item. | Financial | PDF File |
| SYD | =SYD(cost, salvage, life, period) | This function calculates the depreciation of an item throughout its life, using the sum of the years digits | Financial | PDF File |
| Age Calculation | DATEDIF() and Today() | You can calculate a persons age based on their birthday and todays date. | Function Examples | PDF File |
| Brackets in formula | | The use of brackets in formula is to ensure that the calculations are performed in the order that you need. | Function Examples | PDF File |
| Ordering Stock | | This is an example of a spreadsheet to calculate the best time interval to order stock. | Function Examples | PDF File |
| Percentages | | There are no specific functions for calculating percentages. You have to use the skills you were taught in your maths class at school. | Function Examples | PDF File |
| Project Dates | | Sample modle of Project Dates | Function Examples | PDF File |
| Split Text | | These formulas are useful when you have one cell containing text which needs to be split up. | Function Examples | PDF File |
| Time Calculation | | You can subtract two time values to find the length of time between. | Function Examples | PDF File |
| Time Sheet | | This is the simple example of Time Sheet | Function Examples | PDF File |
| CELL | =CELL("TypeOfInfoRequired",CellToTest) | This function examines a cell and displays information about the contents, position and formatting. | Information | PDF File |
| COUNTBLANK | =COUNTBLANK(RangeToTest) | This function counts the number of blank cells in a range. | Information | PDF File |
| ISBLANK | =ISBLANK(CellToTest) | This function will determine if there is an entry in a particular cell. | Information | PDF File |
| ISERR | =ISERR(CellToTest) | This function tests a cell and shows TRUE if there is an error value in the cell. | Information | PDF File |
| ISERROR | =ISERROR(CellToTest) | This function tests a cell or calculation to determine whether an error has been generated. | Information | PDF File |
| ISEVEN | =ISEVEN(CellToTest) | This function tests a number to determine whether it is even. | Information | PDF File |
| ISLOGICAL | =ISLOGICAL(CellToTest) | This function tests a cell to determine whether the cell contents are logical. | Information | PDF File |
| ISNOTEXT | =ISNONTEXT(CellToTest) | This functions tests an entry to determine whether it is a number, rather than text. | Information | PDF File |
| ISNUMBER | =ISNUMBER(CellToTest) | This function examines a cell or calculation to determine whether it is a numeric value. | Information | PDF File |
| ISODD | =ISODD(CellToTest) | This function tests a number to determine whether it is odd. | Information | PDF File |
| ISTEXT | =ISTEXT(CellToTest) | This functions tests an entry to determine whether it is text. | Information | PDF File |
| NA | =NA() | This function is a place marker used to indicate that required information is Not Available. | Information | PDF File |
| AND | =AND(Test1,Test2) | This function tests two or more conditions to see if they are all true. | Logical | PDF File |
| IF | =IF(Condition,ActionIfTrue,ActionIfFalse) | This function tests a condition. | Logical | PDF File |
| NOT | =NOT(TestToPerform) | This function performs a test to see if the test fails. (A type of reverse logic). | Logical | PDF File |
| OR | =OR(Test1,Test2) | This function tests two or more conditions to see if any of them are true. | Logical | PDF File |
| CHOOSE | =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29) | This function picks from a list of options based upon an Index value given to by the user. | Lookup | PDF File |
| HLOOKUP | =HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted) | This function scans across the column headings at the top of a table to find a specified item. | Lookup | PDF File |
| INDEX | There are various forms of syntax for this function. | This function picks a value from a range of data by looking down a specified number. | Lookup | PDF File |
| INDIRECT | =INDIRECT(Text) | s function converts a plain piece of text which looks like a cell address into a usable cell reference. | Lookup | PDF File |
| Lookup (Array) | =LOOKUP(WhatToLookFor,RangeToLookIn) | 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. | Lookup | PDF File |
| LOOKUP (Vector) | =LOOKUP(WhatToLookFor,RangeToLookIn,RangeToPickFrom) | This function looks for a piece of information in a list, and then picks an item from a second range of cells. | Lookup | PDF File |
| MATCH | =MATCH(WhatToLookFor,WhereToLook,TypeOfMatch) | This function looks for an item in a list and shows its position. | Lookup | PDF File |
| SUM with OFFSET | =OFFSET(range, rows, columns, height, width) | This combination of functions is used when necessary to base a calculation on a set of cells in different locations. | Lookup | PDF File |
| TRANSPOSE | =TRANSPOSE(Range) | This function copies data from a range, and places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns. | Lookup | PDF File |
| VLOOKUP | =VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted) | This function scans down the row headings at the side of a table to find a specified item. | Lookup | PDF File |
| CEILING | =CEILING(ValueToRound,MultipleToRoundUpTo) | This function rounds a number up to the nearest multiple specified by the user. | Mathematical | PDF File |
| COUNTIF | =COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched) | This function counts the number of items which match criteria set by the user. | Mathematical | PDF File |
| FLOOR | =FLOOR(NumberToRound,SignificantValue) | This function rounds a value down to the nearest multiple specified by the user. | Mathematical | PDF File |
| GCD | =GCD(Number1,Number2,Number3... through to Number29) | This function calculates the largest number which can be used to divided all the values specified. | Mathematical | PDF File |
| INT | =INT(Number) | This function rounds a number down to the nearest whole number. | Mathematical | PDF File |
| LCM | =LCM(Number1,Number2,Number3... through to Number29) | This function calculate the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers. | Mathematical | PDF File |
| MMULT | =MMULT(Range1,Range2) | This function multiplies one range of values with another range of values. | Mathematical | PDF File |
| MOD | =MOD(Number,Divisor) | This function calculates the remainder after a number has been divided by another number. | Mathematical | PDF File |
| MROUND | =MROUND(NumberToRound,MultipleToUse) | This function rounds a number up or down to the nearest multiple specified by the user. | Mathematical | PDF File |
| ODD | =ODD(NumberToBeRounded) | This function rounds a number up to the next highest whole odd number. | Mathematical | PDF File |
| PI | =PI() | This function is equal to the value of Pi. | Mathematical | PDF File |
| POWER | =POWER(NumberToBeRaised,Power) | This function raises a number to a user specified power. | Mathematical | PDF File |
| PRODUCT | =PRODUCT(Number1,Number2,Number3... through to Number30) | This function multiples a group of numbers together. | Mathematical | PDF File |
| QUOTIENT | =QUOTIENT(NumberToBeDivided,Divisor) | This function calculates the number of times a number can be divided by another number. | Mathematical | PDF File |
| RAND | =RAND() | This function creates a random number >=0 but <1. | Mathematical | PDF File |
| RANDOMBETWEEN | =RANDOMBETWEEN(LowLimit,HighLimit) | This function produces a random whole number between two specified numbers. | Mathematical | PDF File |
| ROUND | =ROUND(NumberToRound,DecimalPlacesToUse) | This function rounds a number to a specified amount od decimal places. | Mathematical | PDF File |
| ROUNDDOWN | =ROUNDDOWN(NumberToRound,DecimalPlacesToUse) | This function rounds a number down to a specified amount of decimal places. | Mathematical | PDF File |
| ROUNDUP | =ROUNDUPNumberToRound,DecimalPlacesToUse) | This function rounds a number up to a specified amount of decimal places. | Mathematical | PDF File |
| SUM | =SUM(Range1,Range2,Range3... through to Range30) | This function creates a total from a list of numbers. | Mathematical | PDF File |
| SUMIF | =SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal) | This function adds the value of items which match criteria set by the user. | Mathematical | PDF File |
| SUMPRODUCT | =SUMPRODUCT(Range1, Range, Range3 through to Range30) | This function uses at least two columns of values. | Mathematical | PDF File |
| SUM (Running Total) | =SUM($D$7:D7) | This function creates running total from the list of number. | Mathematical | PDF File |
| TRUNC | =TRUNC(NumberToTuncate,Precision) | This function removes the decimal part of a number, it does not actually round the number. | Mathematical | PDF File |
| AVERAGE | =AVERAGE(Range1,Range2,Range3... through to Range30) | This function calculates the average from a list of numbers. | Statistical | PDF File |
| COUNT | =COUNT(Range1,Range2,Range3... through to Range30) | This function counts the number of numeric entries in a list. | Statistical | PDF File |
| COUNTA | =COUNTA(Range1,Range2,Range3... through to Range30) | This function counts the number of numeric or text entries in a list. | Statistical | PDF File |
| FORECAST | =FORECAST(ItemToForeCast,RangeY,RangeX) | This function uses two sets of values to predict a single value. | Statistical | PDF File |
| FREQUENCY | =FREQUENCY(RangeOfData,ListOfIntervals) | This function compares a range of data against a list of intervals. | Statistical | PDF File |
| LARGE | =LARGE(ListOfNumbersToExamine,PositionToPickFrom) | This function examines a list of values and picks the value at a user specified position in the list. | Statistical | PDF File |
| MAX | =MAX(Range1,Range2,Range3... through to Range30) | This function picks the highest value from a list of data. | Statistical | PDF File |
| MEDIAN | =MEDIAN(Range1,Range2,Range3... through to Range30) | =MEDIAN(Range1,Range2,Range3... through to Range30) | Statistical | PDF File |
| MIN | =MIN(Range1,Range2,Range3... through to Range30) | In the following example the =MIN() function has been used to find the lowest value for each region, month and overall. | Statistical | PDF File |
| MODE | =MODE(Range1,Range2,Range3... through to Range30) | This function displays the most frequently occurring number in a group of numbers. | Statistical | PDF File |
| PERMUT | =PERMUT(PoolToPickFrom,ItemsInAGroup) | This function calculates the maximum number of permutations given a fixed number of items. | Statistical | PDF File |
| QUARTILE | =QUARTILE(RangeToBeExamined,QuartileValue) | This function examines a group of values and then shows the values which are of the upper limits of the 1st, 2nd, 3rd and 4th quarters of the data. | Statistical | PDF File |
| RANK | =RANK(NumberToRank,ListOfNumbers,RankOrder) | This function calculates the position of a value in a list relative to the other values in the list. | Statistical | PDF File |
| SMALL | =SMALL(ListOfNumbersToExamine,PositionToPickFrom) | This function examines a list of values and picks the value at a user specified position in the list. | Statistical | PDF File |
| STDEV | =STDEV(Range1,Range2,Range3 through to Range30) | This function calculates the sample population standard deviation of a list of values. | Statistical | PDF File |
| STDEVP | =STDEVP(Range1,Range2,Range3 through to Range30) | This function calculates the standard deviation of a list of values. | Statistical | PDF File |
| TREND | =TREND(KnownYs,KnownXs,RequiredXs,Constant) | This function predicts values based upon three sets of related values. | Statistical | PDF File |
| VAR | =VAR(Range1,Range2,Range3 through to Range30) | This function calculates the sample population variance of a list of values. | Statistical | PDF File |
| VARP | =VARP(Range1,Range2,Range3 through to Range30) | This function calculates the variance of a list of values. | Statistical | PDF File |
| CONCATENATE | =CONCATENATE(Text1,Text2,Text3...Text30) | This function joins separate pieces of text into one item. | Text | PDF File |
| EXACT | =EXACT(Text1,Text2) | This function compares two items of text and determine whether they are exactly the same. | Text | PDF File |
| LEFT | =LEFT(OriginalText,NumberOfCharactersRequired) | This function displays a specified number of characters from the left hand side of a piece of text. | Text | PDF File |
| LEN | =LEN(Text) | This function counts the number of characters, including spaces and numbers, in a piece of text. | Text | PDF File |
| LOWER | =LOWER(TextToConvert) | This function converts all characters in a piece of text to lower case. | Text | PDF File |
| MID | =MID(OriginalText,PositionToStartPicking,NumberOfCharactersToPick) | This function picks out a piece of text from the middle of a text entry. | Text | PDF File |
| PROPER | =PROPER(TextToConvert) | This function converts the first letter of each word to uppercase, and all subsequent letters are converted to lower case. | Text | PDF File |
| REPLACE | =REPLACE(OriginalText,StartPosition,NumberOfCharactersToReplace,NewText) | This function replaces a portion of text with a new piece of text. | Text | PDF File |
| REPT | =REPT(TextToRepeat,Repetitions) | This function repeats a piece of text a specified number of times. | Text | PDF File |
| RIGHT | =RIGHT(OriginalText,NumberOfCharactersRequired) | This function displays a specified number of characters from the right hand side of a piece of text. | Text | PDF File |
| SUBSTITUTE | =SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse) | This function replaces a specified piece of text with a different piece of text. | Text | PDF File |
| TEXT | =TEXT(NumberToConvert,FormatForConversion) | This function converts a number to a piece of text. | Text | PDF File |
| TRIM | =TRIM(TextToTrim) | This function removes unwanted spaces from a piece of text. | Text | PDF File |
| UPPER | =UPPER(TextToConvert) | This function converts all characters in a piece of text to upper case. | Text | PDF File |
| VALUE | =VALUE(TextToConvert) | This function converts a piece of text which resembles a number into an actual value. | Text | PDF File |
