Free Microsoft Excel 2013 Quick Reference

## In desperate need of grading formula

Freshman-year calculus 29 years ago was the last serious mathematical thinking I engaged in, so please bear with me.

My school has a new grading formula which requires not only a given grade median, but also a set grade distribution.

If I have a column of grades which range from 4.0 to 8.5, what formula will yield me a column that ranges from 4.5 to 11.0, with all the intervening grades curved proportionately?

Hi All,
Long time reader first time poster here!
I have an excel 2007 spreadsheet that holds students grades as a percentage.
What I'd like to do initially is display them on a scale from 0-100 with the student name next to each mark. I could do this fairly easily using Vlookup but I can't figure out how to accommodate students with the same mark. On a simple worksheet the names just overwrite each other, I can't understand how I'd get it formatted like this......

60
59 Wayne
58
57 Bob,Jim,Sarah,Tim
56
55 Alex
54

any ideas? Is it a job for a macro or am I missing something simple

Many thanks
Steve

Hi there

I am trying to perfect a spreadsheet that will analyse the stats of grades. Their name, gender, predicted grade and actual grade are entered into columns A, B, C & D respectively, commencing in cell B38.

I am trying to perfect a formula that will tell me the grades of BOYS only that score D,M,P and FAIL (D=Distinction, M=Merit, P=Pass) and express it as a % of the BOYS only grades.

As a test I entered the grades & genders of 3 boys in cells A38-D40, inputting D for distinction in all three actual grade colums. I also inputted grades for 4 girls too. In cell Y5 I used the following formula to calculate the % of boys getting a D as an actual grade (which should be 100% as all 3 boys got D).

=SUMPRODUCT(--(\$B\$38:\$B\$131="M"),--(\$D\$38:\$D\$131="D"))/ COUNTIF(\$B\$38:\$B\$131,"?*")

The result of this formula is 42.9% which is the right result IF I wanted the % of BOYS & GIRLS that got a D. I am having brain freeze here - anyone want to point out the obvious and help me just to isolate the boys grades?

Hey guys I'm new to the forums so I'm sorry if some of the questions that I'm asking are noobish :/

Anyway, I have received a task to do for school and I tried my heart out but I can't seem to figure this out. It's really simple but I can't seem to do it... I'm not very good with excel.

The excel file is attached.

Basically, I need to create a gradebook for a professor.

* Use the appropriate formulas for rows 21-25
* Column G must contain ONE formula that can be filled down the column
o IF the student took all exams (total defined in B27), the formula will drop the lowest grade and average the rest of the exams
o IF the student did not take all the exams, it will just take an average of the exams taken
* Column H will contain a formula that rounds Column G to the nearest whole number
* Column J will contain a formula that does the following:
o IF there is a "YES" in column I, the formula must check if the student's rounded average (in column H) is equal or greater than 65. If it is, the formula will output a PASS instead of a letter grade. If it is lower than 65, it will output an F
o If there is no "YES", it will check the rounded average to the grade table and output a letter grade
* Column K will rank the student's grade in column G against the rest of the class

Finally, print out a bar chart that displays each of the students' final "number" grades and also labels each of the bars with the appropriate students' name. Use the capabilities of Excel to do this, which means that you should take the students' names from the original spreadsheet and have them linked into your bar chart.

I got most of it done but there are still some things wrong.

Here's what I'm having problems with:
1)The formula in column J must be the same for each row (meaning it must
determine if column I says yes or not)
2)The grade distribution should be calculated (I need a formula that detects how many A's, B's, C's, etc.. there are and put in in the grade distrubition.

Help would be greatly appreciated
Thank you..Attachment 45873

## Grading on a Curve - Formula question

I'm trying to help a friend that is a teacher and wants to make a spreadsheet that grades on a curve.

I've seached and found the following -

assuming Grades are in A10:A20, then (A10-AVERAGE(A\$10:A\$20))/STDEV(A\$10:A\$20) gives the standard deviation of a particular score.

Also, I found something that said a distribution for grades would look something like this -

-3.25 F
-1.5 D
-1 C
0.1 B
1.1 A

THEREFORE, =VLOOKUP((A10-AVERAGE(A\$10:A\$20))/STDEV(A\$10:A\$20), \$X\$3:\$Y\$7,2) yields a grade for the score in A10. Using this, I get pretty much the distribution I want - 10-15% A's, 30-40% B's, 30-40% C's, 10-15% D's and F's.

My challenge is to convert the StdDev into actual Scores, not just grades. At the end of the year, it's hard to calculate 10 A's, 5 B's, 1 C, so it would be great to assign an actual score BASED ON A CURVE. Essentially, for each quiz, the score they get might be a 40, but based on a curve their score might actually be a 95 or whatever since the 40 was the highest score. I could assign an F a 50, a D a 60, a C a 70, etc, but I'm curious if there is a way to assign a score to every number between 1 and 100. So -4 StdDev is a 1, a -3.95 is a 2, all the way to anything greater than 1.5 is an A+.

Is there a formula that could do this or even a lookup table that would have the right increments? Or am I completely off or totally bonkers.

I'd appreciate any suggestions

## Forced Distribution

Is there a mechanized way to take a series of grades and bump them up or down
to fit a forced distribution, eg. no more than 10% get A's, 30% B's etc...

Thanks,

Mark

## Distributed retrival:average value

This might belong in Exchange rather than here but anyways:
I need to send out a sheet with a number of things in need of being graded
(1 to 5).
When I get them back (several hundred) I need to get the average value
shouldn't be used to calculate the average. Another one of the problems here
is that all the files returned might /might not have the same name.

## How can we classify referring to grades?

Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total capacity
of students.
On application Each candidate also supplies priorities for himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65 respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to such
problems or provide a code that I can work on.
TIA
J_J

## Trying to place students into groups w/ equal distribution of race/gender/etc

I have a an excel worksheet with 500+ students with a variety of fields, the important ones being Student Name, Race, Gender, and Grade. I need to create approximately 20 groups of 25-27 people that are comprised of a somewhat even representation of Race, Gender, and Grade Level.

Is there an automated way to do this through excel?

Would appreciate the help as situations like this are common throughout the year and if I could figure this out it would make my life so much easier!

Thanks for any help!

## Creating a distribution Graph.

How do I build a graph to denote the distribution of the Excel data?

Believe it or not, this was our boys 4th grade homework.

## Simulation Model For Inventory

hello,
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Thank you very much for any help you can provide, as it is greatly appreciated.

Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).

Component MTBF(months)
1 15
2 24

Below are Company B's sales records for the past 36 months.

Month Sales Month Sales Month Sales
1 10 13 9 25 7
2 15 14 13 26 10
3 17 15 14 28 18
4 22 16 20 28 18
5 25 17 23 29 21
6 26 18 24 30 22
7 20 19 19 31 17
8 19 20 17 32 15
9 19 21 16 33 15
10 17 22 13 34 12
11 14 23 12 35 10
12 11 24 9 36 8

The parts distribution center manager would like to know for month 37 what should be the monthly inventory level of each replacement component to assure fill rates of 98% for component 1 and a 95% fill rate for component 2.
Develop a simulation model using Excel and VBA to determine the inventory needed for each component. Your grade will be based on how well the model answers the question and how easy it is for a user to run the model, change the parameters and interpret the results. Answers should be based on 10,000 replications of the model.

## Exploded Pie Chart

I have to creat an exploding pie chart depicting the distribution of letter grade grades. I can sort the grades but when i try to make a pie chart I get a vertical line?

How do I depict "alpha values" in a pie chart Tab 2 Column 2.

## Creating Random numbers in Excel problem

Hi all

I have a difficult one here.

1. I have to make a VBA makro that can generate calculus pieces (2 numbers added to each other) for 2.nd grade students. The Program has to generate Random easy calsulus as shown on the
picture. It has to be 30 pieces of (+) Caluculus distributed with 5 horisontally and 6 vertically.

2. A user has to be able to put a minimum and a maximum value of the sum of numbers generated. So if the user puts 20 and 40 the sum of the numbers it is creating can only be between those 2 number. say 11+11, 22+10 etc.

3. Also the student has to be able to try and solve the solve the calculations by typing in his or her answer under the randomly generated numbers.

4. VBA
Then has to go through all the calculations to see if the student made a
right, wrong or no answer at all.

5. The final results will have to be displayed in a message box. e.g. "there are
30 assignments solved with the following results, 22 correct, 1 missing and

## Conditional formatting and more than one criteria

I have very little experience with excel and was hoping someone on here could help. I am currently coaching track and would like to distribute awards based on whether or not an athlete has achieved a specific time, however, those times are different based on their grade level (6th grader, 7th grader or 8th grader) and particular event (100m, 200M, 400M etc.) I was hoping I could input their times beside their names and as they achieve the established goal the cell would change colors. Is there any way to do this? Thanks.

## Extracting data to form a new table

As per the example, I have a spread sheet comprising 4 pairs of columns headed Option and Grade (numbered 1 to 4). Col A is the Name of a person, cols B to I contain option (subject name) and grade data. Not every option and grade pair is used and the distribution is random.

In each used pair the grade is always one cell to the right of the subject. For each person name, I want to extract all of the grade data and place it into a separate table having separate columns for each subject i.e. who got which grade for the range of subjects.

By using the lookup function, I am able to identify where the subject and grade pairs are for each row. I have been unable then to move one cell to the right to extract the grade letter. I have thought of combining OFFSET with LOOKUP but can’t make it work. Any help would be much appreciated please.

## Solid conditional formatting Excel

Hello, new on the forum but really stuck on excel. I'm using Excel 2007 and trying to conditionally format a series of cells to illustrate the percentage distribution of a total.

I.e

cell A1:100,000
A2:35%
A3:65%
A4:Conditional formatting to highlight distribution

On the conditional formatting I can only find a graded scale but need the bar to be a solid fill with clear definition of the two values ideally with a midpoint bar.

Any help on this matter is greatly appreciated.

Regards,

Mark