MMULT (Returns the matrix product of two arrays)

What Does It Do?

This function multiplies one range of values with another range of values.

The ranges do not have to be of equal size.

The dimensions of the result range is in direct proportion to dimensions of the two input ranges.

It is an Array function and must be entered using the Ctrl+Shift+Enter combination.

Syntax

=MMULT(Range1,Range2)

Formatting

No special formatting is needed.

Example

The following tables were used by a company producing boxes of chocolates.

The types of chocolate produced were Milk, Dark and White.

The company boxed the chocolates in three differing mixtures of Milk, Dark and White.

In the run up to Christmas customers ordered various quantities of each box.

The chocolate company now needed to know what quantity of each type of chocolate to produce.

The =MMULT() function was used to multiply the contents of boxes by the customer orders.

The result of the =MMULT() is the total number of each type of chocolate to produce.

How It Was Done

Cells C36 to E36 were selected.

The formula =MMULT(C32:E32,C26:E28) was typed, (but not yet entered).

The keys Ctrl+Shift+Enter were pressed to confirm the entry as an array.

The formula then showed the correct result.

Getting The Dimensions Correct

The dimensions of the Result range are directly related to the two input ranges.

The number of rows in the Result should be equal to the rows in Range1.

The number of columns in the Result should be equal to the columns in Range2.

Example

The following tables were used by the chocolate company to calculate the amount of ingredients needed to produce batches of chocolate.

The company has four factories, each of which has to order enough Butter, Eggs and Sugar to ensure they can meet production targets.

Range 1 contains the planned production of Milk and Dark chocolate for each factory.

Range 2 contains the amount Butter, Eggs and Sugar needed to make 1 unit of Milk or Plain.

The Result range shows the quantities of each ingredient that will have to be ordered to meet the production target.

Note the depth of the Result is the same as the depth of Range 1, and the width of the Result is the same as the width of Range 2.

Hint

To get a feel for how the =MMULT() function operates, set all values in Range1 and Range2 to zero 0, then change a single value in each.

MOD (Returns the remainder from division)

What Does It Do?

This function calculates the remainder after a number has been divided by another number.

Syntax

=MOD(Number,Divisor)

Formatting

No special formatting is needed.

MODE (Returns the most common value in a data set)

What Does It Do?

This function displays the most frequently occurring number in a group of numbers.

For it to work correctly there must be at least two numbers which are the same.

If all the values in the group are unique the function shows the error #N/A.

When there is more than one set of duplicates, the number closest to the beginning of the group will be used. (Which is not really an accurate answer!)

Syntax

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

Formatting

No special formatting is needed.

Example

The following table shows garments sold in a clothes shop.

The shopkeeper wants to keep track of the most commonly sold size.

The =MODE() function has been used to calulate this.

Note

If the =AVERAGE() function had been used the answer would have been :           9.7

This figure is of no benefit to the shopkeeper as there are no garmets of this size!

MONTH (Converts a serial number to a month)

What Does It Do?

This function extracts the month from a complete date.

Syntax

=MONTH(Date)

Formatting

Normally the result will be a number, but this can be formatted to show the actual month by using Format,Cells,Number,Custom and using the code mmm or mmmm.

Example

The =MONTH function has been used to calculate the name of the month for your birthday.

MROUND (Returns a number rounded to the desired multiple)

What Does It Do?

This function rounds a number up or down to the nearest multiple specified by the user.

Syntax

=MROUND(NumberToRound,MultipleToUse)

Formatting

No special formatting is needed.

NA (Returns the error value #N/A)

What Does It Do?

This function is a place marker used to indicate that required information is Not Available.

It can be type directly in to a cell as =NA() or it can be used as part of a calculation.

When the =NA() is used, any calculations which depend upon the cell will also show #NA.

It is used to indicate that all the data has not yet been entered in to the spreadsheet.

Syntax

=NA()

Formatting

No special formatting is required.

Example

The following table was used by a company to calculate the monthly Wage of an employee.

The Salary and Tax percentage are entered.

The Tax is then deducted from the Salary to calculate the Wage.

Table 1 shows that when the Tax is not entered, the Wage is still calculated.

On a large spreadsheet this may go unnoticed and the wrong Wage paid.

Table 1

Table 2 shows how the =NA() has been inserted in the unknown Tax to act as a reminder that the Tax still needs to be entered.

Table 2

Networkdays (Returns the number of whole workdays between two dates)

What Does It Do?

This function will calculate the number of working days between two dates.

It will exclude weekends and any holidays.

Syntax

=NETWORKDAYS(StartDate,EndDate,Holidays)

Holidays : This is a list of dates which will be excluded from the calculation, such as Xmas   and Bank holidays.

Formatting

The result will be shown as a number.

Note

The calculation does not include the last day. The result of using 1-Jan-98 and 5-Jan-98 will give a result of 4. To correct this add 1 to the result. =NETWORKDAYS(Start,End,Holidays)+1

Example

The following example shows how a list of Holidays can be created.

Not (Reverses the logic of its argument)

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.

Now (Returns the serial number of the current date and time)

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.

Odd (Rounds a number up to the nearest odd integer)

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.

OR (Returns TRUE if any argument is TRUE)

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.

Example

The following table shows a list of orders taken by a company.

A handling charge of Rs. 5/- is made on all orders paid by Visa or Delta cards.

The =OR() function has been used to determine whether the charge needs to be applied.

Ordering Stock

This is an example of a spreadsheet to calculate the best time interval to order stock.

Scenario

A garage fits exhaust systems.

The manager orders the exhausts on a regular basis.

Each time an order is made for new stock, there is a fixed administrative cost.

The exhausts are kept in stock until needed.

Keeping the exhausts in stock incurs a cost due to capital tied up and warehouse costs.

The supplier of the Exhausts gives a discount on large orders.

Objective

Find the time interval to order stock which will result in the lowest Admin and Warehouse costs.

Input Data

Output Data

Things to try

                        Change the Discount % to 0% and 0%.

                        Change the Ordering Interval to 1 or 30.

                        Change the Cost of the Exhaust making it cheaper or more expensive.

                        Change the Quantity used per day to a larger or smaller number.

Explanation

Column A         Ordering Interval In Days

The first of these cells has the value 1 entered in it.

This is the smallest ordering period, which would require stock to be ordered every day.

The second cell picks the ordering interval from the Input Data table.

The third and subsequent cells add the ordering interval to the previous cell to create a list

of values of the same interval.

Column B         Quantity Per Order

                        This is the number of Exhausts which will need to be ordered.

                        Calculation: OrderingInterval * QuantityUsedPerDay

Column C         Order Value

                        This is the value of the Order before any discount.

                        Calculation: QuantityOrdered * CostOfExhaust

Column D         Order Discount

The discount which can be subtracted from the order value.

The discount is only given on orders which are equal to or greater than the

Price Break values set by the supplier.

Calculation: OrderValue * SupplierDiscount

                   The supplier discount is calculated using the =IF() and the =AND() functions.

       If the OrderQuantity is equal to or above the first Price Break, but below the

                   second Price Break, then the first Price Break discount is used.

                   =C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0))

                  If the OrderQuantity is equal to or above the second Price Break, the second

                  Price Break discount is used.

                  =C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0))

                  If the OrderQuantity does not qualify for a discount, zero discount is used.

                 =C29*IF(AND(B29>=$G$24,B29<$G$25),$H$24,IF(B29>=$G$25,$H$25,0))

Column E         Orders Per Year

This is how many orders will need to be made based upon the ordering interval.

With an interval of 1, there will have to be 365 orders.

                       

Calculation: 365/OrderingInterval

                   This calculation may give results which are decimal, such as 2.3

                   This decimal will cause problems, due to the fact that the number of orders

                   must always be a whole number.

                   The =CEILING() function has been used to 'round up' any decimals to the next

       highest whole number.

       =CEILING(365/A29,1)

Column F         Annual Admin Costs

This is the administration costs involved in making the orders.

Calculation: OrdersPerYear * AdminCost

                   =E29*$G$20

Column G        Annual Warehouse Costs

This is the cost of keeping the stock in the warehouse.

It is based on the managers knowledge that on average the stock level is 50% of the

quantity ordered.

Calculation: QuantityOrdered * AverageStockLevel) * ExhaustCost * WarehousingCost

                   =(B29*$G$21)*$G$17*$G$18

Column H         Annual Total

This is the full yearly cost of ordering the Exhausts, based upon how frequently the

orders are made.

It does not take in to account the actual costs of the Exhausts, as the manager only

wants to know what the lowest values for the overheads associated with ordering and

storing the exhaust systems.

However, the Discount figure is taken into account as this can be used to offset some

of the overheads.

                       

Calculation: AnnualAdminCosts + AnnualWarehouseCosts - OrderDiscount

                                           =F29+G29-D29

Column I          The Best Ordering Interval

This shows the Best ordering interval, giving the lowest annual overheads.

It compares the value in column H against the minimum value for all of column H.

If the two values match the word Best is shown, otherwise a dash is shown.

=IF(H29=MIN($H$29:$H$59),"Best","-")