Calculations & Formulae
Calculations worksheet
Most of the calculations are found in the Calculations sheet in the system.
Cell link Cell Range Column Row
VLOOKUP(**,*****,*)
Sex
Cell E4 contains the formula =VLOOKUP(D4,Drivers,3)
This looks up the 3rd column on the Drivers cell range and D4 is the number of the row
Date
Cell I3 contains the formula =NOW()
This displays the date and time.
Passenger Seats
Cell I6 contains the formula =VLOOKUP(D7,Cars,3)
This looks up the 3rd column on the Cars cell range and D7 is the number of the row
Car Hire Cost
Cell I7 contains the formula =VLOOKUP (D7,Cars,4)
This looks up the 4th column on the Cars cell range and D7 is the number of the row
Mileage Pay
Cell I10 contains the formula =VLOOKUP(D11,Mileage,3)
This looks up the 3rd column on the Mileage cell range and D11 is the number of the row
Cell B23 contains the formula =VLOOKUP(D11,Mileage,2)
Cell B24 contains the formula =VLOOKUP(D7,Cars,2)
Cell B25 contains the formula =VLOOKUP(D4,Drivers,2)
Duty Pay
Cell I8 contains the formula =I7*D8
This multiplies the values from cell I7 and D8
Friday Night Bonus
Cell E17 returns TRUE if check box is checked, If unchecked = FALSE
Cell F17 contains the formula =IF(E17=TRUE,FridayBonus,"")
If cell E17 shows TRUE (checked checkbox) then the value FridayBonus (which is found in the Rates sheet, is returned in cell F17. If its unchecked then cell F17 is left blank.
Full Weeks Work Bonus
Cell E18 returns TRUE if check box is checked, If unchecked = FALSE
Cell F18 contains a similar formula to cell F17 but returns the value of FullWeekBonus (also found in the Rates sheet) instead of FridayBonus
Tips
There are two ways of entering data into C20. By clicking on the actual cell or clicking on the Tips button which brings up an Input box. The cell is validated to only accept values from 0 to 1000. See Validation section for more info.
If the value put in the input box isn’t from 0 to 1000 then the cell is left blank
Total Income
Cell I23 contains the formula =SUM(I8:I20)
Adds the sum of values found from cells I8 to I20
Tax
Cell I25 contains the formula =IF(I23>80,TaxRate*I23,"0")
If the value of I23 is larger than 80, then the TaxRate (value found in Rates sheet) is multiplied by I23 and the value returned in I25.
If the value from I23 isn’t larger than 80 then 0 is returned in I25
After Tax
Cell I26 contains the formula =I23-I25
The value of I25 is subtracted from I23 is returned into I26
Data sheet
This sheet is for referencing data from the Calculations sheet. Therefore whatever data is changed in the Calculations sheet, will also be changed automatically in the Data sheet.
Cell A2 =Calculations!B25 - Looks up cell B25 in the Calculations sheet
Cell B2 =Calculations!B24
Cell C2 =Calculations!D8
Cell D2 =Calculations!I8
Cell E2 =Calculations!B23
Cell F2 =Calculations!I10
Cell G2 =Calculations!E17
Cell H2 =Calculations!E18
Cell I2 =Calculations!F20
Cell J2 =Calculations!I17
Cell K2 =Calculations!I23
Cell L2 =Calculations!I25
Cell M2 =Calculations!I26
Cell N2 =Calculations!I3
Print Out sheet
This sheet is a template ready for printing and displays information from the Data sheet which gets it’s info from looking up the Calculations sheet.
Rates sheet
These formulas were used so that if cells E14, E16 and E19 had content deleted a warning statement would appear in the cell next them
E14 =IF(D14="","Don't Leave blank!","")
E16 =IF(D16="","Don't Leave blank!","")
E19 =IF(D19="","Don't Leave blank!","")
Income sheet
At the top half of the sheet, data that has been transferred from the Data sheet is stored.
Column J is named Total_Bonus
Column K is named Total_Income
Column L is named Tax
Column M is named After_Tax
Column N is named Date
All Drivers’ Bonus Total
D23=SUM(Total_Bonus)
Average
E23=AVERAGE(Total_Bonus)
Highest
H23=MAX(Total_Bonus)
Lowest
I23=MAX(Total_Bonus)
All Drivers’ Total Income
D24=SUM(Total_Income)
Average
E24=AVERAGE(Total_Income)
Highest
H24=MAX(Total_Income)
Lowest
I24=MIN(Total_Income)
(After tax)
D25=SUM(After_Tax)
Total Tax (Bosses Wages)
D26=SUM(Tax)
Cell A18 is used to calculate how many days are in between the oldest and newest record. The cell is formatted to one decimal place and is made discrete with white Font
Cell A18 = MAX(Date)-MIN(Date)
Cell A17 =
IF(A18>7,"Over a week's amount of records, you may wish to delete some old records","")
A17 returns a message to advises you to delete old records once the number of days in between the oldest and latest record is more than 7
Validation on Data Entry
Rates sheet
Cell D14 only accepts values from 5 to 50. If value doesn’t fall in that range or isn’t a number, the following error message emerges.
Cell D16 only accepts values from 100 to 250. If value doesn’t fall in that range or isn’t a number, the following error message emerges.
Cell D19 only accepts values from 0 to 0.3 If value doesn’t fall in that range or isn’t a decimal, the following error message emerges.
Data can also be entered in cells D14, D16 and D19 through input boxes. These input boxes are loaded when the buttons besides these cells are clicked.
If the Friday Night Bonus button is pressed this input box would pop up.
The input box has been programmed to have the same validation as of Cell D14
If the value isn’t from the range of 5 to 50 then cell D14 is left blank.
Calculations sheet
Days on Duty
Cell D8 is validated to only accept whole numbers from the value of 1 to 7
Error message appears if incorrect data is entered into cell
Tips
Cell C20 will only accept data from the value of 0 to 1000
Cell C20 like the data entry cells in the Rates sheet, has two ways of entering data. Manually by clicking and typing into cell or clicking the button labelled Tips.
If the incorrect data is entered into the cell manually an error message will appear similar to the ones from the validated cells, in the Rates sheet.
The button runs the Tips_Input macro, which brings up an input box that enters data into C20.
If the value entered in the cell isn’t between 0 and 1000, or isn’t a number, cell C20 shall be left blank.
Macros and Buttons
User Forms
Welcome From
The front end of the spreadsheet program is the Welcome user form. The spreadsheet isn’t accessible without going through first because it’s loaded from the auto_open macro.
These first few lines in the auto_open macro bring up the Welcome form at the start.
Sub auto_open()
Load WelcomeForm
WelcomeForm.Show
The limousine and title images was placed into the form using the picture box control and the Picture property to use the files limo.jpg and logo.jpg respectively.
The label tool was used to write out “Wage Calculator” and the address.
The form has 3 Buttons on it: Exit, Enter and About
This button brings up a message box with vb yes and no buttons.
Code:
Private Sub cmdExit_Click()
Response = MsgBox("Do you wish to exit?", vbYesNo + vbExclamation, "Exit")
If Response = vbYes Then
Application.Quit
End If
End Sub
This button hides the form and enables access to the spreadsheet.
Code:
Private Sub cmdEnter_Click()
WelcomeForm.Hide
End Sub
This button shows the About form
Code:
Private Sub cmdAbout_Click()
AboutForm.Show
End Sub
About Form
This form is accessed through the About button on the welcome form or clicking on the Harvey’s logo on the calculations sheet. The picture box shows the file JessicaHarvey.jpg and the text shown on the form were created with the use of the label tool.
The Close button hides the form
Code:
Private Sub cmdClose_Click()
AboutForm.Hide
End Sub
