Free Microsoft Excel 2013 Quick Reference

- In desperate need of grading formula
- Student Grades Distribution Chart
- Grade Distribution
- Grading and averaging
- Grading on a Curve - Formula question
- Forced Distribution
- Distributed retrival:average value
- How can we classify referring to grades?
- Trying to place students into groups w/ equal distribution of race/gender/etc
- Creating a distribution Graph.
- Simulation Model For Inventory
- Exploded Pie Chart
- Creating Random numbers in Excel problem
- Conditional formatting and more than one criteria
- Extracting data to form a new table
- Solid conditional formatting Excel

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?

Thanks in advance for all your help.

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

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?

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.

Here are the task specifications:

You must accomplish all of the following in your task:

* 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

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

to fit a forced distribution, eg. no more than 10% get A's, 30% B's etc...

Thanks,

Mark

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

(grade) of the returned answers. Some answers will not be entered and hence

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.

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

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!

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

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.

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

spreadsheet attached.

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

3 wrong answers".

Please see attached photo for a better understading.

Thanks in advance. If any questions please ask me.

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.

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