Used Car Database Project                    © Mike Splane               Business 96X

 

These Functions might be useful for this project. To see examples of these Functions, download this Excel file http://bus91l.altervista.org/Data/96XData.xlsx and look in the Functions Tab.

COUNT – counts the number of cells in a range that are numbers.

COUNTA – counts the number of cells in a range that are not empty. It counts cells with numbers, characters, text, or strings.

COUNTBLANK – counts the number of cells in a range that are completely empty.

COUNTIF – Counts the number of cells in a range that meet a given condition: =6, >5, =”salad” etc.

MOD – Divides one number by another number and shows the remainder. Useful for sorting. For example, if you divided a column of dates by 7, =MOD(C4,7), all of the dates that showed the same MOD value would occur on the same day of the week. (2 = Monday, 5 = Thursday)

NOW () - Shows the current date and time. Excel combines the date (an integer) with the time of day (a decimal value).

TODAY () - Shows the current date. Excel stores this value as an integer.

INDIRECT – converts a text string into a cell reference. =Indirect(“C4”) will return the cell reference C4.

Indirect Function     Time = 8:16     https://www.youtube.com/watch?v=cjBEBn6YMi0

OFFSET – Returns a reference to a cell that is a given distance away from a given cell reference.

Offset Function     Time = 3:55     https://www.youtube.com/watch?v=xvDEaLZXTPI

MAX – Returns the value if the largest number in a set of numbers.

MIN - Returns the value if the smallest number in a set of numbers.

 

ASSIGNMENT

For this project you will create a table (range) in Excel listing 1000 used cars for sale.  There will be eleven columns (fields) in the table. You will create the information to fill the table. Here are the eleven fields and their required information.

ID Number = A four-digit number. Each car will have a different number ranging from 1000 to 1999

Method:

Formulas/Functions:

Difficulties:

Make = Use five to eight different makes: Toyota, Honda, Ford, GMC and at last one other.

Method:

Formulas/Functions:

Difficulties:

Model = At least fifteen different models. Some carmakers will have more models listed than others.

       Method:

Formulas/Functions:

Difficulties:

Year = Years will range from 2008 to 2016.

Method:

Formulas/Functions:

Difficulties:

Mileage = Mileage will range between 8000 and 16000 miles per year. Older cars will be higher.

Method:

Formulas/Functions:

Difficulties:

 

Price = Prices will be higher for newer cars and lower mileage cars.

Method:

Formulas/Functions:

Difficulties:

State = The five western states. Use a two-digit value for each state. CA, OR, WA, AZ, and NV.

Method:

Formulas/Functions:

Difficulties:

City = Use at least eight cities and at least one city in each state. The tricky part is matching the cities to the states.

Method:

Formulas/Functions:

Difficulties:

Owners = the number of previous owners, from 1 to 4.   Higher numbers are rarer. How will you adjust for that?

Method:

Formulas/Functions:

Difficulties:

Accidents = the number of accidents a car has been in, from 0 to 4. Higher numbers are rarer. How will you adjust for that?

Method:

Formulas/Functions:

Difficulties:

Date Listed for Sale = A random date for each car, within the past 180 days.

Method:

Formulas/Functions:

Difficulties:

Days Listed = A calculation, the current date minus the date listed set to update automatically.

Method:

Formulas/Functions:

Difficulties:

 

After the table is completed, you will need to develop a macro to get the customer’s requirements. Comments or labels to guide the customer through the process are required. 

After the use’s requirements are obtained you need to figure out how to filter the data to find the cars that match the requirements. This video may be helpful      Extracting Data     Time = 12:07

After the search is completed, you will need to create presentation graphics to display the result to the customer.  The graphics should contain a picture of the make and model.

You will also need to develop a way to update the database to add and remove cars from the inventory.

 

Deliverables

The deliverables will be a Solutions Sheet for your team, noting which contributions were made by each member, and an Excel file with the working version of your project. Please include the team members’ names in the Excel file.

 

Example of a Solution Sheet

Objective: In the A1:A1000 range enter a consecutive list of numbers between 1000 and 1999.

 

Method: Enter the number 1000 in cell A1. Create a formula that adds 1 to the number found in the cell above it. Select the A2:A1000 range.  Use Ctrl + Enter to enter the formula into the range.

Note: Two other possible methods are typing in each number by hand and using the AutoFill handle.

 

Formulas and Functions:  =A1+1

 

Difficulties: 

I don’t know how to select the range A2:A1000 quickly and efficiently without using the scroll-down method.

            Solution: I can type A2:A1000 into the name box to quickly select the range.

I don’t know if the cell reference to A1 will change to refer to new rows as I copy it down using Ctrl + Enter. It might keep the cell reference as A1.

            Solution: Excel updated the row references so this was not a problem.