Spreadsheet

Spreadsheet

1. Functions

   • MAX - I have used =MAX on page 13 in cell B3 to work out the maximum order total to see the maximum amount that customers are spending
   • MIN - I have used =MIN on page 13 in cell B$ to work out the minimum order total to see the minimum that customers are spending
   • SUM - I have used =SUM on page 5 in cell F21 to work out the line total to see how much the customer has spent to work out the overall total
   • LOOKUP - I have used =LOOKUP on page 3 in cell B21 to lookup the product to then find out the product number to ensure the correct product is delivered
   • Macro - I have used a navigational macro on page 1 in cell F18 to take the user from the welcome page to the invoice page to make it more user friendly
   • Absolute Addressing - I have used a $ on page 5 in cell E21 to ensure that when the formula is copied the cells within the formula don't change ensuring it is accurate
   • Sort - I have used a sort on page 17 in cell A9 to sort the invoice number from lowest to highest to see which order is the oldest
   • Filter - I have used a filter on page 14 in cell C9 to filter just women to see how much they are spending and target them with special offers and discounts
   • Graph - I have used a bar chart on page 20 to show the amount in stock to see which products need reordering
   • Single IF
   • Multiple IF

2. Validation

   • Length Check - I have used a length check on page 6 in cell B13 on the telephone number to make sure 11 characters are entered as all telephone numbers contain 11 characters
   • Range Check - I have used a range check on page 9 in cell B3 to make sure the invoice number is between 1 and 1000000 as the number of invoices doesn't exceed 1000000

3. Simplifying Data Entry

   • Combo Box - I have used a combo box on page 3 in cell A21 to select the product that is being ordered making it quicker to enter the data
   • Spinner - I have used a spinner on page 4 in cell D21 to select the quantity of the product being ordered making it quicker to enter the data