CSCI 1250 - Project #2

CSCI 1250 - Excel Assignment #2

Important Notice: Read all the requirements below.

The first 8 calculations from columns H to O only require 8 initial calculations that you will need to copy down to the other cells in the column. If done correctly, you shouldn’t need to modify any of the calculations once copied. Know when and where to use absolute cell references. Absolute Cell References will not be as common as the first assignment but you will need to use them in a handful of functions.

Note: Anyone caught copying or cheating from anyone or any website/app such as Discord or ChatGPT, will have paperwork filed for academic dishonesty with the university. I do NOT recommend going online to search for solutions. You will usually find the wrong way to solve the problem.

● This assignment must be completed and returned to me via D2L

● You must use MS-Excel to complete all 12 steps of the assignment.

● Use only functions unless instructed to use a formula. There is only 1 place in the assignment that it is specified that it is okay to use a formula.

● Do not use literal numbers or literal character strings in calculations unless instructed.

● Always complete the calculations inside a single cell unless otherwise instructed. Also leave cells where they are unless otherwise instructed. For instance, do not copy/move values from one sheet to another.

---------------------------------------------------------------------------------------------------------------------

● Download the file SalesIncentives.xlsx from D2L. Make sure when you submit your file later that you submit the modified file and not a copy of the unmodified file you downloaded.

Setup

1. On row 44, add your name and choose 1 of the 4 territories for yourself (Central, Eastern, Southern, or Western).

2. For the 2023 Sales cell in your row, type in the number 100 _ _ _ where the last three digits are the last three digits of your 900 student number at school.

● For example, if my 900 number was 900180994, then I would enter 100994 into cell C44.

3. In Cells D44 through G44, enter the numbers 13,001 13,001 13,001 12,345.

Goals

1. For column H, compute the Total 2024 Actual amount by adding the 4 quarters of 2023 together for each employee. Use a function for your calculation. If your Excel worksheet displays a tiny green wedge in the upper left corner-it is a warning that does not apply here if you are doing the calculation correctly. You can ignore the warning.

2. Now calculate the column 2024 Goal by taking the value in the 2023 Sales column and multiplying it by the Goal % Increase. You can find the Goal Percent Increase on the Values worksheet. Do not move or copy the Goal Percent Increase value.

3. In the range C49:E49, compute the total number of employees that have met the requirement labels in cell C48:E48 based on their Total 2024 Actual earnings. Use the Countif function to do this.

● Because of the nature of this function, you are allowed to use literal text criteria for the 2nd parameter. For example, “>50” would find things that exceeded 50.

4. Calculate the % Goal Met column by taking the Total 2024 Actual and dividing it by the 2024 Goal. Use a formula to do this as the quotient function will not work properly in this scenario.

Bonuses

1. In column K, use a function to rank which employees had a higher % Goal Met. You may find the use of the Insert Function button, next to the Formula Bar, useful here.

● If created correctly, the first employee is ranked to have the 18th ranking and the second employee has the 7th ranking.

● If you have more than one employee with the same rank, you may need to use an absolute value somewhere. Also, if you aren’t getting the rank 18 and 7 for the first two employees, it may mean you made a mistake in a previous calculation.

2. In column L, use a function to determine if an employee is ranked in the top ten. If an employee ranks in the top ten, then put the text “Top 10” in column L next to their rank value. If the employee is not in the top ten, put a blank space in this column next to their rank.

● Hint: If it says “False”, you will want to replace that with a blank string of text. You may use the literal number 10 in this calculation.

3. In column M, determine how much bonus a particular sales associate should be getting. This is based on their performance. Using their rank value, determine their bonus using the percentages on the Values worksheet along with the Total 2024 Actual value.

● If someone has a rank in the top ten, then you would multiply their value from column H with the value in B5 of the Values worksheet.

● If they were ranked between 11 and 22, you would multiply their value from column H with the value in B6 of the Values page.

● If they were ranked above 22, then they would just receive the flat rate value stored in B7 of the Values page.

● You may use literal number values in this calculation to determine which bonus category someone falls into.

● Remember that you are not allowed to use a formula for multiplication here. ● If you get stuck on this calculation move on and come back to it.

String Manipulation

1. In column N, with one calculation, use the Search and Left functions together to extract the last name from each of the employee names from column B and display them in column N. Do not use the Find function.

● Be aware that the first word in column B is the last name and that a comma is what separates the last name from the first name.

● Search for the comma not a blank as some of the employees have two word last names.

● Be aware that there are many different length last names. ● Extra Credit: You may keep the comma in the result, but you are also allowed to

use a subtraction of one in the calculation to eliminate the comma. Use a function if you do this. Do not use a formula.

2. In column O, use the following functions: Lower, Replace and Find to take the Territory from column A and replace the word “Territory” with the word “USA” and then output the final result all in lowercase letters.

● For example, cell O8 would contain the words western usa as a result. ● You may use both the literal words “Territory” and “USA” and also the literal

number 9 in your functions. ● I know there are alternate ways to solve this with other functions, but I want you

to only use the Replace and Find functions.

When submitting your file, make sure you submit the updated version of the file.