## Study Unit 4 / 5: Creating user-friendly spreadsheets

Study unit 4: Creating user-friendly spreadsheets  - page 52
Study unit 5: Spreadsheet formulas and functions - page 96

Section 2 merely emphasises some basic mathematics, accounting, and such like the knowledge that you should already have.

Remember that when you write out the excel formulas in the exams there are some differences in the way symbols are entered.

The table below highlights the differences:

 Mathematics Microsoft office excel Addition + + Subtraction - - Multiplication x * Division ÷ / Equal = = Brackets/Parentheses ( ) ( ) Greater then > > Exponents a2 ^ Less then < < Greater than or equal to >= >= Less than or equal to <= <= Not equal to ≠ <>

Order of operation – excel operates in the same way as B E D M A S

1st - computations/calculations in parentheses (/) Brackets, no matter where they are in the formula

2nd - computations with Exponents

3rd - computations involving Division (/) and Multiplication (*) from left to right

4th - computations involving Additions (+) and Subtraction (-) from left to right

EXAMPLE OF ORDER OF OPERATION

SOLVE THE FOLLOWING: 150+150 / (2+12)*12/4

 Sequence Formula Reason 1st (2+12)=14 Computations/calculations in parentheses/brackets no matter where they are in the formula 2nd 150/14=10.714 Computations involving multiplication (*) and division (/) from left to right. The 14 relates to the answer calculated in the 1st sequence 3rd 10.714*12=128.568 Computations involving multiplication (*) and division (/) from left to right. The 10.714 relates to the answer calculated in the 2nd sequence 4th 128.568/4=32.142 Computations involving multiplication (*) and division (/) from left to right. The 12.568 relates to the answer calculated in the 3rd sequence 5th 150+32.142=182.142 Computations involving addition (+) and subtraction (-) from left to right. The 32.142 relates to the answer calculated in the 4th sequence

Basic accounting principles that require formulas in excel

Gross profit = Sales – Cost of sales

GP% = Gross Profit/Sales *100/1

Sales =cost of sales + mark up

• In excel usually, the mark up and cost are usually a cell reference
• For example, a stock cost R100 and we mark it up by 50%

REAL WORLD FORMULA

100x50%= 50

100+50 =150 = selling price

EXCEL FORMULA

=Cell(cost)*Cell(mark up%)+Cell(cost)

Or

=Cell(cost) *(1+Cell (mark up%)

• Vat inclusion and exclusion calculations
• Sometimes vat calculation require we add or remove the vat
• To add vat the formula would be

=Cell(ex vat amount)*(1+Cell (Vat %))

TO REMOVE VAT THE FORMULA WOULD BE

=Cell(Incl VAT)*100/(1+Cell(Vat%))

Or

=Cell(incl vat)/(1+Cell(Vat%))

Common errors when using formulas and functions

#DIV/0! – This error occurs when the formula calls for division by a cell that either contains the value 0 or as is more often the case the cell is empty. Division by zero is not possible according to mathematical principles

#NAME – this error occurs when Microsoft Office Excel does not recognize text in a formula

#NULL! – this error occurs most often when you insert a space where you should have used a comma to separate cell references used as arguments for functions

#NUM –  this error occurs when Excel encounters a problem with a number in a formula, such as the wrong type of argument in a an function or a calculation that produces a number too large or too small to be represented in the worksheet

#REF! – this error occurs when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over cells referred to in a formula

#VALUE! – this appears when you use  the wrong type of argument in a function, the wrong type of operator or when you try to do a mathematical operation that refers to cells containing text entries.

#N/A!- this error occurs when a value is not available to a function of a formula

Structure of a function

Structure                                                              The formula

Begins with = sign                                              =

Function name                                                   SUM

Opening parenthesis                                         (

The arguments for the function                      Cell1:Cell2

Closing parenthesis                                          )

Combing text and values (&)

We use the & to combine the contents of two separate cells

The formula is cell reference 1 & cell reference 2

1. a1&a2

Should you wish to add text or a special character between the two cell references we need to use inverted comma’s

Let’s say we are combining ain and 2601 and would like a – in between the ain and the 2601

The formula would be =cell reference1&”-“&cell reference2

In the above example the answer would be AIN-2601

Commonly used functions – simple exam marks

Sum

=Sum(cell1:cell2)

Round

=Round(cell1,number_of_digits)

Average

=Average(cell1:cell2)

Maximum

=Min(cell1:cell2)

Minimum

=Min(cell1:cell2)

Left

=Left(cell reference, number of characters)

This function is used when you wish to extract a series of numbers or letters (including special characters) from a string of characters that start on the left hand side of the string.

1. You wish to extract XY from the product code XY456RTG

Right

=Right(cell reference, number of characters)

This function is used when you wish to extract a series of numbers or letters (including special characters) from a string of characters that start on the right-hand side of the string.

1. You wish to extract PQ from the product code THR324PQ

Mid

=Mid(cell reference, start number, number of characters)

This function is used when you wish to extract a series of numbers or letters (including special characters) from a string of characters that start in the “middle” of the string. The reason middle is in inverted commas is that this function can be used to extract any number of characters between the first and the last digit.

1. You wish to extract ABC from the product code THR32ABC4SD

Logical functions

This function is used when you wish to perform a lookup that has two possible outcomes. The function calculates if something is true and returns a particular result and if not returns a different result.

For example = let’s say boys wear blue shirts and girls wear pink shirts the logical lookup will check if a person is a boy and then allocate a blue shirt to that person and if not will allocate a pink shirt.

=IF(logical_test,value_if_true,value_if_false)

The “logical test value” checks to see if the lookup is true

The “value if true” references what you want to achieve if the test is true

The “value if false” references what you want to achieve if the lookup is not true

Lookup functions

The vertical lookup function is used when you wish to look up something in one area of a spreadsheet with a corresponding common item in another area of a spreadsheet with the goal of returning a related item to the lookup

For example – we may have a list of student numbers with names and assignment marks. In another area of the spreadsheet, we have the same list of student numbers with exams marks.

We can use the vertical lookup to search the common list of student numbers and then fetch the exam mark and put it next to the assignment mark.

Vertical lookup = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup value – This is the value in the current area of the spreadsheet you would like to look up in another area of the spreadsheet. ie. the student number.

Table array – this is the area in the spreadsheet that contains the data you wish to look up and return to the current areas in the spreadsheet. ie. the student numbers and exam marks

Column index – this is the column number in the table array that contains the answer you are looking for. ie. the exam mark. Please note this is a number.

Range lookup – the range lookup tells excel if you are looking for an exact match or an approximation when it looks for the answer.  ie. for an exam mark we want an exact match. For this, we indicate false for exact and true for approximate matches.

Financial functions

These functions are used to calculate the repayment of a loan value, the future value of an amount invested or the present value of a series of future payments.

Calculation of payment                  =PMT(rate,nper,pv,fv,type)

Calculation of future value            =FV(rate,nper,pmt,pv,type)

Calculation of present value         =PV(rate,nper,pmt,fv,type)

rate = the interest rate quoted. Please note that interest rates are quoted annually and should the question state a monthly installment we need to convert the interest rate to a monthly amount. We achieve this by dividing the interest rate by 12.

nper = the applicable period. The repayment or investment term is normally stated in year. Please note that should the question state a monthly installment we need to convert the period to months. We achieve this by multiplying the term by 12.

pmt = the payment/instalment amount

fv = Future value of the investment or asset or loan

pv = Present value of the investment or asset or loan

type = 1 if the installment is paid at the beginning of the month

type = 0 if the installment is paid at the end of the month

General note – remember that in accounting a negative answer indicates cash out flow. Think about the question from your perspective. If you are working out a payment for a loan, this would go out of your pocket and therefore is an outflow which would therefore be a negative answer.

Unisa often asks for the answer to return a positive value. You achieve this by inserting a minus sign in the front of the function name or in front of the pmt or fv or pv argument depending on the function being used.

Frequent mistakes in Microsoft office excel questions

Not starting the formula with =

 Correct Lose 1 mark =sum(B5:B19) Sum(B5:B19) =if(C3>B4,500,1000) If(C3>,500,1000)

Using [ ] instead of ( )

 Correct Lose 1 mark =sum(B5:B19) =sum[B5:B19] =if(C3>B4,500,1000) =if[C3 > B4,500,100]

Incorrect use of parentheses ( )

 Correct Lose 1 mark =sum(B5:B19) =sum(B5:B19 =if(C3>B4,500,100) =(if C3>B4,500,100) =sum(B5:B19) =sumB5:B19)

Using ; instead of : or

 Correct Lose 1 mark per incorrect syntax =sum(B5:B19) =sum(B5;B19) – 1 mark lost =if(C3.B4,500,1000) =if(C3.B4;500;1000) – 2 marks lost

Using SUM in a formula where it is not necessary

 Correct Lose 1 mark =B5+B10 =sum(B5+B10) =B10*B4 =sum(B4*B10) =B5/B10 =sum(B5/B10)

Not using “” when using text in a formula

 Correct Lose 1 mark per incorrect syntax =if(C3=”yes”,”open”,”close”) =if(c3=yes,”open”,”close”) – 1 mark lost =if(C3=”yes”,”open”,”close”) =if(C3=”yes”,open,close) – 2 marks lost =if(C3=”yes”,”open”,”close”) =if(C3=yes,open,close) – 3 marks lost

Using mathematical signs instead of Microsoft Office Excel signs

 Correct Lose 1 mark * x / ÷

Using a formula when a function is available

 Correct Lose 1 mark =sum(B5:B10) =B5+B6+B7+B8+B9+B10 =average(B5:B10) =(B5+B6+B7+B8+B9+B10)/6

Incorrect writing of values/amounts in a formula or a function

 Correct Lose 1 mark 0.1 0,1 100000 100 000 or R100000 or R100,00

Expressing percentages incorrectly

 Correct Lose 1 mark 5% 0r 5/100 or  “100 or 5/100 *100) 0.05 14% or 14/100 or  or “100 or 14/100”100 0.14