Guidebox: FUNCTIONS

Showing posts with label FUNCTIONS. Show all posts

Saturday, 2 June 2018

FUNCTIONS June 02, 2018

Microsoft Excel Function : BIN2DEC

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.

GuideBox 0

Thursday, 31 May 2018

FUNCTIONS May 31, 2018

Microsoft Excel Function : 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 which contain text or blanks use =SUM() to get the total and then divide by the count of the entries using =COUNTA().

GuideBox 0

FUNCTIONS May 31, 2018

Microsoft Excel Function : 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

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.

GuideBox 0

FUNCTIONS May 31, 2018

Microsoft Excel Function : 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 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.