Rules
for Creating Formulae
There
are rules that must be obeyed when constructing a spreadsheet
or mathematical formula. Often formulae include a collection of
different operators, +, -, *, / . There is a rule that identifies
in which order you should carry out the calculation. BODMAS
is an acronym used in which to remember this order:
Brackets
Over
Divide
Multiplication
Addition
Subtraction.
Calculate
the contents of brackets before any division, then any multiplication
before addition and finally subtraction, in that order. Consider
the following formulae:
(a)
10+12/2=
(b) (10+12)/2=
Enter
these two equations into a spreadsheet and compare the answers
(eg = 10+12/2). Following the rules laid out in BODMAS, example
(a) gives the answer as 16 (Division before Addition). Example
(b) gives the answer 11 (the contents of Brackets before Division).
In
our Payroll example we need to consider the effect of taxation
on the employees. Assume that each employee is allowed to earn
£3,744 a year before taxation.
- Recall
Payroll example 2.
- Insert
3 rows above the Employee details.
- In
cell B2 enter Tax Threshold.
- In
cell D2 enter 3744.
- Change
the title in cell E5 to Weekly Gross Pay.
- Insert
a new title in cell F5 called Tax Allowance.
Although
it is not advisable to use numbers in formula, since it is unlikely
that the 52 weeks in a year will change for simplicity we will
make an exception in this case.
- Enter
the formula =D2/52 in cell F6.
- Repeat
this formula to calculate the Tax Allowance for each employee.
- Enter
Basic Rate of Tax in cell B3, and .24 in cell D3.
- Enter
a new title in cell G5 called Tax.
The
tax for each employee is obtained by multiplying the Basic Rate
of Tax by the amount of pay left after the employee receives his
tax allowance (ie (Weekly Gross Pay - Tax Allowance)* Basic Rate
Of Tax).
- Enter
the formula =(E6-F6)*D3 in G6.
- Repeat
this formula to obtain the tax paid by each employee.
- Format
cell D3 to show percentage.
- Format
cells in column G to show two decimal places.
 Contents:
Spreadsheets
Skill
Check: Spreadsheets
|
