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.  I’d suggest putting the numbers in a largest t smallest order. It will make it easier to update the data range later on when you are adding more cars.

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

Can you do a custom list to populate this column?

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

Hint: Start by building a table listing makers in the first column and models in columns to the right. Then create a lookup function to find a model to match the maker.

Hint: use a RANDBETWEEN function as the third argument in your LOOKUP function.

Hint: When you build your lookup table, you can have the same model appear in multiple columns, so each carmaker has an equal number of models to choose from.

·         Year = Years will range from 2008 to 2016.

=RANDBETWEEN 2008, 2016)

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

= (2016 -  Model year) * RANDBETWEEN(8000,16000)

·         Price = Prices will be higher for newer cars and lower mileage cars. How will you set up this formula?

·         State = The five western states. Use a two-digit value for each state. CA, OR, WA, AZ, and NV. Can you do a custom list to populate this column?

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

·         Owners = the number of previous owners, from 1 to 4.    =RANDBETWEEN(1,4)

Higher numbers are rarer. How will you adjust for that?

·         Accidents = the number of accidents a car has been in, from 0 to 4. =RANDBETWEEN( 0,4)

Higher numbers are rarer. How will you adjust for that?

·         Date Listed for Sale = A random date for each car, within the past 180 days. =RANDBETWEEN((TODAY()-180),TODAY())

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

=Today() – date listed for sale.

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.

This video will help you to create message boxes for the customers to input their choices.    Time = 10:22

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

My thoughts on this would be to have a second set of fields, duplicating all of the fields in the original database, listed to the right of the original data range. You could have formula for each field that applied a 1 or 0 value to each car, based on whether or not the customer selected that field and based on the value that was selected. If the user asked to see all of the Fords, that column would have a one value applied to all the cars in the original data range that had a Ford listing and a zero value applied to all of the rest.  If the customer did not select the field, all of the values in that column would be zero.  At the end of this second data range you could have a sum function that combined the scores. You could then sort the entire set of both data ranges in Descending order to bring the cars that match the customer’s preferences to the top. Then you would have to apply a filter to bring the matching models to the top. You could use the MAX function to find the cars that match the most criteria.

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. This video shows how: Showing Pictures Using a Validation List     Time = 6:51

The graphics should list all of the data in the eleven categories. The display should be visually appealing. A summary label should also be included: XXX cars found. View this COUNTIFS video for help in extracting records from a table based on multiple criteria.     Time = 12:42

You will also need to develop a way to update the database to add and remove cars from the inventory. Think about it as four separate steps.

1.       Create a form for information to be entered about a car that will be added to the data range. This does not have to use the form tool. It could be labels next to input cells, with a message telling the use to enter the data.  The input cells could contain data validation rules. There should be a macro button for the user to push after he has finished entering the data, to trigger the next step.

2.       Create a method to bring the data from the form into the used car data range in the worksheet.  The tricky part will be assigning an ID number to the car that hasn’t already been used. I would write my macro to do these steps.

·         Sort the data range by ID number, with the largest number on top.

·         Insert a new row at the top of the data range, just below the field labels.

·         In the new row, for the ID Number, write a formula adding 1 to the value in the cell right below it.

·         In the rest of the fields in that row, enter formulas with cell references to where the user input the data about the new car.

·         Use copy and paste special to replace the formulas with their values.

·         Clear the user input fields.

3.       Create a rule for when to remove cars from the data range. For instance, after 180 days the car should be removed. You could use the rule IF TODAY() - date listed >180 to filter the data.

4.       Create a method to delete the information about the cars that should be removed. There should be a macro button to push to cleanse the data.

Remove duplicate rows    Time = 6:10

This video has a cool way to do it, but it doesn’t delete all of the unwanted rows. We can fix that. Just add a row at the beginning of the data range that contains the value you want to use as your delete criteria before using the remove duplicates option.

If you can figure out a method to select all of the rows that contain the value to be used as the deletion criteria, you could right-click on the mouse to get rid of all of the rows.

How to Update Excel Worksheet Data with User Form     Time = 19:58

This video is not closed-captioned.