A To Z of Excel Functions | Series 4
A To Z of Excel Functions - Series 4
91. NOT FUNCTION
What Does It Do ?
This function performs a test to see if the test fails. (A type of reverse logic).
If the test fails, the result is TRUE.
If the test is met, then the result is FALSE.
Syntax
=NOT(TestToPerform)
The TestToPerform can be reference to cells or another calculation.
Formatting
No special formatting is needed.
Example
The following table was used by a library to track books borrowed.
The date the book was Taken out is entered.
The period of the Loan is entered.
The date the book was returned is entered.
The =NOT() function has been used to calculate whether the book was returned within
the correct time, by adding the Loan value to the Taken date.
If the book was not returned on time the result Overdue is shown, otherwise OK is shown.
92. NOW FUNCTION
What Does It Do ?
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.
Syntax
=NOW()
Formatting
The result will be shown as a date and time. If it is formatted to show as a number
the integer part is used for the date and the decimal portion represent the time.
 |
| CLICK TO LEARN MORE & DOWNLOAD! |
93. ODD FUNCTION
What Does It Do?
This function rounds a number up to the next highest whole odd number.
Syntax
=ODD(NumberToBeRounded)
Formatting
No special formatting is needed.
94. OR FUNCTION
What Does It Do?
This function tests two or more conditions to see if any of them are true.
It can be used to test that at least one of a series of numbers meets certain conditions.
Normally the OR() function would be used in conjunction with a function such as =IF().
Syntax
=OR(Test1,Test2)
Note that there can be up to 30 possible tests.
Formatting
When used by itself it will show TRUE or FALSE.
95. PERMUT FUNCTION
What Does It Do ?
This function calculates the maximum number of permutations given a fixed number of items.
The internal order is significant, so AB and BA will be considered as two possible permutations.
It could be used to calculate the possible number of 4 digit passwords from the digits 0 to 9.
Syntax
=PERMUT(PoolToPickFrom,ItemsInAGroup)
Formatting
No special formatting is needed.
96. PI FUNCTION
What Does It Do?
This function is equal to the value of Pi.
It is correct to 15 decimal places.
It does not need any input, it is a self contained function.
Syntax
=PI()
Formatting
No special formatting is needed.
97. POWER FUNCTION
What Does It Do?
This function raises a number to a user specified power.
It is the same as using the ^ operator, such as 3^4, which result is 81.
Both the POWER() function and the ^ operator are the same as using 3*3*3*3.
Syntax
=POWER(NumberToBeRaised,Power)
Formatting
No special formatting is needed.
Example
To calculate the area of a circle.
98. PRODUCT FUNCTION
What Does It Do?
This function multiples a group of numbers together.
It is the same as using 2*3*5*10*3*7, which results in 6300.
Syntax
=PRODUCT(Number1,Number2,Number3... through to Number30)
or
=PRODUCT(RangeOfNumbers)
or
=PRODUCT(Number1,Range,Number2...)
Formatting
No special formatting is needed.
99. PROPER FUNCTION
What Does It Do?
This functions tests an entry to determine whether it is text.
If the entry is text is shows TRUE.
If the entry is any other type it shows FALSE.
Syntax
=PROPER(TextToConvert)
Formatting
No special formatting is needed.
100. QUARTILE FUNCTION
What Does It Do?
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.
The Quartile of 0 (zero) is actually lowest value, which can be obtained using the =MIN() function.
The Quartile of 4 is actually highest value, which can be obtained using the =MAX() function.
Syntax
=QUARTILE(RangeToBeExamined,QuartileValue)
The QuartileValue can only be 0,1,2,3 or 4.
Formatting
The result will be formatted as a normal number.
101. QUOTIENT FUNCTION
What Does It Do?
This function calculates the number of times a number can be divided by another number.
It ignores any remainder, only showing the whole number.
Syntax
=QUOTIENT(NumberToBeDivided,Divisor)
Formatting
No special formatting is needed.
102. RAND FUNCTION
What Does It Do?
This function creates a random number >=0 but <1.
The number will change each time the worksheet recalculates, or when F9 is pressed.
Syntax
=RAND()
Formatting
No special formatting is needed.
103. RANDBETWEEN FUNCTION
What Does It Do?
This function produces a random whole number between two specified numbers.
The random number will change each time the spreadsheet is recalculated or F9 is pressed.
Syntax
=RANDOMBETWEEN(LowLimit,HighLimit)
Formatting
No special formatting is required.
104. RANK FUNCTION
What Does It Do?
This function calculates the position of a value in a list relative to the other values in the list.
A typical usage would be to rank the times of athletes in a race to find the winner.
The ranking can be done on an ascending (low to high) or descending (high to low) basis.
If there are duplicate values in the list, they will be assigned the same rank. Subsequent ranks
would not follow on sequentially, but would take into account the fact that there were duplicates.
If the numbers 30, 20, 20 and 10 were ranked, 30 is ranked as 1, both 20's are ranked as 2, and the 10 would be ranked as 4.
Syntax
=RANK(NumberToRank,ListOfNumbers,RankOrder)
The RankOrder can be 0 zero or 1.
Using 0 will rank larger numbers at the top. (This is optional, leaving it out has the same effect).
Using 1 will rank small numbers at the top.
Formatting
No special formatting is needed.
105. REPLACE FUNCTION
What Does It Do?
This function replaces a portion of text with a new piece of text.
You need to specify where the replacement should start, how many characters to
remove and what the new replacement text should be.
Syntax
=REPLACE(OriginalText,StartPosition,NumberOfCharactersToReplace,NewText)
Formatting
No special formatting is needed.
106. REPT FUNCTION
What Does It Do?
This function repeats a piece of text a specified number of times.
You need to specify the text to be repeated and how many times to repeat it.
Syntax
=REPT(TextToRepeat,Repetitions)
The maximum number of repetitions is 200.
Formatting
No special formatting is needed.
 |
| Learn More & Download Grading & Result App |
107. RIGHT FUNCTION
What Does It Do?
This function displays a specified number of characters from the right hand side of a
piece of text.
Syntax
=RIGHT(OriginalText,NumberOfCharactersRequired)
Formatting
No special formatting is needed.
108. ROMAN FUNCTION
What Does It Do?
This function produces a number shown as Roman numerals in various formats.
Syntax
=ROMAN(NormalNumber,RomanNumberFormat)
The RomanNumberFormat can be any of the following.
0 is Classic. This is used if no format is specified.
1 is more Concise.
2 is even more Concise.
3 is even more Concise still.
4 is Simplified.
TRUE is Classic
FALSE is Simplified
Formatting
No special formatting is needed.
Note
There is no function to do the opposite calculation of Roman to normal.
109. ROUND FUNCTION
What Does It Do?
This function rounds a number to a specified amount of decimal places.
If 0 is used the number is rounded to the nearest whole number.
If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax
=ROUND(NumberToRound,DecimalPlacesToUse)
Formatting
No special formatting is needed.
110. ROUNDDOWN FUNCTION
What Does It Do?
This function rounds a number down to a specified amount of decimal places.
If 0 is used the number is rounded down to the nearest whole number.
If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax
=ROUNDDOWN(NumberToRound,DecimalPlacesToUse)
Formatting
No special formatting is needed.
111. ROUNDUP FUNCTION
What Does It Do?
This function rounds a number up to a specified amount of decimal places.
If 0 is used the number is rounded up to the nearest whole number.
If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax
=ROUNDUPNumberToRound,DecimalPlacesToUse)
Formatting
No special formatting is needed.
112. SECOND FUNCTION
What Does It Do?
The function will show the second of the minute based upon a time or a number.
Only the fraction part of the number is used as it is this which relates to time of day.
Syntax
=SECOND(Number)
Formatting
The result will be shown as a normal number between 0 and 59.
113. SIGN FUNCTION
What Does It Do?
This function tests a value to determine whether it is positive or negative.
If the value is positive the result is 1.
If the value is negative the result is -1.
If the value is zero 0 the result is 0.
Syntax
=SIGN(CellToTest)
The CellToTest can be a cell or a calculation.
Formatting
No special formatting is needed.
114. SLN FUNCTION
What Does It Do?
This function calculates the Straight Line Depreciation of an item.
(Also known as Fixed Instalment method).
The Straight Line Depreciation is how much the value of an item reduced during a specific
period of time. The result is a uniform depreciation value.
An example would be if you bought a new car for £20,000, then kept it for 6 years.
At the end of your ownership you sell the car for £8,000.
The difference between the original and the trade in price is £20,000 - £8,000 which is £12,000.
Because you owned the car for 6 years, the SLN is calculated as £12,000 / 6 which is £2,000.
Syntax
=SLN(OriginalCost,SellingPrice,LengthOfOwnership)
The LengthOfOwnership can be any time period, days, months or years.
However, the SLN which is calculated will, be for that time, specifying 2 years ownership
as 24 months will give an SLN per month.
Formatting
No special formatting is needed.
115. SMALL FUNCTION
What Does It Do?
This function examines a list of values and picks the value at a user specified position
in the list.
Syntax
=SMALL(ListOfNumbersToExamine,PositionToPickFrom)
Formatting
No special formatting is needed.
116. STDEV FUNCTION
What Does It Do?
This function calculates the sample population standard deviation of a list of values.
A sample population is used when the list of values represents a sample of a population.
Syntax
=STDEV(Range1,Range2,Range3 through to Range30)
Formatting
No special formatting is needed.
117. STDEVP FUNCTION
What Does It Do?
This function calculates the standard deviation of a list of values.
The result is calculated on the basis that the values represent the entire population.
Syntax
=STDEVP(Range1,Range2,Range3 through to Range30)
Formatting
No special formatting is needed.
118. SUBSTITUTE FUNCTION
What Does It Do?
This function replaces a specified piece of text with a different piece of text.
It can either replace all occurrences of the text, or a specific instance.
The function is case sensitive.
Syntax
=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse)
The InstanceToUse is optional, if it is omitted all instances will be substituted.
Formatting
No special formatting is needed.
119. SUM FUNCTION
What Does It Do?
This function creates a total from a list of numbers.
It can be used either horizontally or vertically.
The numbers can be in single cells, ranges are from other functions.
Syntax
=SUM(Range1,Range2,Range3... through to Range30).
Formatting
No special formatting is required.
120. SUMIF FUNCTION
What Does It Do?
This function adds the value of items which match criteria set by the user.
Syntax
=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMIF(C4:C12,"Brakes",E4:E12)
This examines the names of products in C4:C12.
It then identifies the entries for Brakes.
It then totals the respective figures in E4:E12
=SUMIF(E4:E12,">=100")
This examines the values in E4:E12.
If the value is >=100 the value is added to the total.
Formatting
No special formatting is needed.
👈Go To Series 1
👈Go To Series 2
👈Go To Series 3
👉Go To Series 5
Comments
Post a Comment