excel assignment
2-)Calculate the absolute and relative frequencies of each letter grade for female and male students separately. This is, fill in the blank cells of the given tableG1:K13. (10 pts)
3-)Then, draw a horizontal histogram of the relative frequencies(percentages of each letter grade attained by female and male students)in the worksheet titled “BarChart of Q6 here”. Add also data labels to your histogram so that the percent frequencies for each letter grade and for each gender are visible. Note that the sum of the frequencies should add up to 100% both for the female and male students. Your histogram should look like the picture shown in the question.Enjoy and have fun! (5 pts)
• DO NOT USE THE FILTER TOOL!
• Make sure that your percentage values have 1-decimal-digit precision.
HINT: To draw the histogram adequately, go watch the following two Youtube video:
“Learn Excel 2010 – “Comparative Histogram”: Podcast #1522”
The video has been posted on the Youtube channel “BJele123” which belongs to the same person (namely, Bill Jelen) as the channel “ExcelisHell.” The video explains in great detail how to make a comparative and aligned histogram chart in Excel 2007/2010.
Adorable Q7 (15 pts)
In this question you are the executive assistant to the Registrar’s Office at a university with five faculties: Sciences, Law, Economics, Engineering and Social Sciences. Students enrolled in these five faculties are classified into four groups according to their status: FRESHMAN, SOPHOMORE, JUNIOR and SENIOR. In the Add-Drop week students have the right to drop one or more courses, which they have registered during the registration period. For each student who drops at least one course, that course is labeled either as Core, Elective, or Area Required.
Table 1 shows 713 records with student ID and name concealed, but status and faculty indicated. For each record, the course name and label (Core, Elective, Area Required) have been also provided. A student may have dropped multiple courses, which have been aggregated into one record.
Your job is to calculate in Table 2 the number of course drops for each student group using the conditional counting functions of Excel 2007. For instance, what is the total number of FRESHMAN students who dropped the course CHBI 3? Likewise, if there are any, how many SOPHOMORE, JUNIOR and SENIOR students dropped this course? Note that not all 92 courses, but only a subset of them (32 courses) are shown in Table 2, as we are interested in these only.
The total number of students who dropped each of these 32 courses will be computed with a simple SUM() function in Table 2, which has been already written in the last column (column L). At this point, you are also asked to apply conditional formatting to the rows of this table as follows:
If the total number of students who dropped a course is equal to 10 or above, the entire row of that course in Table 2 should be highlighted in yellow and written in bold as in this sentence!
Grading: Conditional Counting worth 10 pts; Conditional Formatting worth 5 pts.
HINT: You are allowed to sort the columns of Table 1 as necessary in order to fill in the cells of Table 2. However, if you use some advanced conditional counting functions of Excel 2007/2010, then you do not have to do that.
Enchanting Q8 (10 pts)
The worksheet of this question lists 40 randomly selected hospitals in Istanbul and their contact information. Each of these hospitals or clinics has a minimum bed capacity of 20, thus it is included in the medical waste collection program of the Metropolitan Municipality of Istanbul.
Each address field contains the name of a particular district in which the hospital or clinic is located. Moreover, all district names of Istanbul are also provided in the lower right corner of the table in cells G42-AR42.
Your job is to fill the district name column (column F) of the table with the missing district names. This way, it will be possible to determine which hospital from which district is covered in the medical waste collection program. You shall import the district name of a hospital from its address field in column E.
Have fun!
MagnificentQ9 (20 pts)
The worksheet of this question contains the X and Y coordinates of 12 cities named as A through L. A distance matrix is shown in the question which is supposed to display the Euclidean distances between any origin/destination pairs. Remember that the Euclidean distance between a pair of points P1 and P2 with coordinates (x1, y1) and (x2, y2) is given by the formula dP1,P2= . Your job is to develop an automatic total distance calculation and tour visualization mechanism for the solution of the traveling salesman problem (TSP) involving all the given 12 cities.
Here is the list of tasks you are asked to accomplish:
1. Derive the cell values of the symmetric distance matrix in B2:M13 using the coordinates table in O2:Q13. (5 pts)
2. For the given TSP solution (i.e., the given TSP tour) in B15:N15, calculate the individual origin/destination (O/D) distances which sum up to the total length of the TSP tour as already computed in B17. (5 pts)
3. Fill in the cells of the table in S2:V14 which is already set up as an Excel table. This table should contain the names and coordinates of all the cities in the same sequence as in the given TSP tour. Since a TSP tour is a closed loop, it must start and end in the same city (Namely, in city C according to the given solution.) (5 pts)
4. Once you have filled up the names and coordinates of the cities in this second table in S2:V14, draw the TSP tour as a scatter plot diagram in the worksheet called “XY-Plot of the TSP Tour in Q9.” Let Excel connect the cities in the same sequence as in column T of the second table. (5 pts)
HINT:
i-) Your XY-plot of the TSP tour should be flexible. This means that it should adjust automatically whenever the given TSP solution in B15:N15 is modified.
ii-) Your TSP tour should show the names (labels) of the cities next to the data marks of the associated scatter plot chart. Do you still remember how to accomplish this job with a third party Excel add-in?

Fantastic Q12 (15 pts)
The following table shows an anonymous customer database with 10 parametric fields (attributes) and 1,000 records (rows) of your merchandising company. The dependentvariable of interest is the realized amount spent by each customer last year. However, you are also interested in the correlation between the independent variables the values of which are shown in the first 9 fields of the database. Now, using each time a different Pivot Table that is well-formatted, answer the following questions in a separate worksheet. For each question:
* You are required to build at least one Pivot Table with appropriate database attributes in the appropriate table fields.
* Format the average spending values in the tables as USD currency with no decimal points. No formatting means no credit!
* Finally, write a comment of two or three sentences as a descriptive answer to each of the following questions.
QUESTION 1
Can we say that women always tend to spend more than men (on the average of course) regardless of marital status and age? Or does the difference in the amounts spent by women and men actually depend on the marital status and age? (5 pts)
QUESTION 2
Does the number of product catalogs mailed to the addresses of customers make a significant impact on their average spending habits? If yes, is this impact more or less the same across different spending histories? (5 pts)
QUESTION 3
Part a) Do you face stiff competition from stores selling merchandise similar to yours?
Part b) Based on your answer to Part a), do you think it would help boost your average sales if you send more catalogs to customers who live in the vicinity of a competitor? Or, is it just the opposite, i.e., should you send a lesser number of catalogs to customers who can easily buy merchandise from your competitors? (5 pts)

BeautifulBONUSQ13 (10 pts)
A construction company has made an agreement with Garanti Leasing to outsource its need for construction trucks. Garanti Leasing has presented the company a list of 20 trucks with their weekly leasing costs as shown in the worksheet named “Truck List”. The agreed pricing scheme is as follows:
If the company decides to lease and use a particular truck Tr-k from Garanti Leasing, then it incurs the weekly leasing cost of Tr-k no matter how many times and for how many kilometers the truck is used that week.
The company operates on numerous construction sites. It has solved a vehicle routing problem to optimize the weekly operating schedule of the trucks to be leased from Garanti Leasing as shown in the table. According to this schedule the company does not lease all 20 trucks, but only a subset of them. Your job is to calculate the applicable weekly leasing fee of each truck and the resulting total weekly leasing cost for the construction company.
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!