How can I write this 'simple' formula

[JEM]

I have a simple spreadsheet for calculating students' grades.

Each class, assignment, or test has a point value (not a percentage).

Example:
A class is worth 20 points
A project spread out over a term may be worth 250 points
A test may be worth 50 points.

At the end of the term I calculate grade percentages based on total points
earned for each category.

10 classes x 20 points = 200 total points
Dividing points earned by (10%) of total point values gives me a percentage.
200 points earned = 100%
160 points earned = 80 %
100 points earned = 50%

Project: 250 points
250 points earned = 100%
200 points earned = 80 %
125 points earned = 50%

Test: 50 points
50 points earned = 100%
40 points earned = 80%
25 points earned = 50%

Now I use MAX to find the highest value in 'COL A'. Then, in 'COL B', I
divide all the numbers in 'COL A' by 10% of the highest value to get a
percentage. (EX: 200 points divided by 20 = 100%, 160 points divided by 20 =
80%)

This is a two step process: Find MAX in 'COL A'. Then in 'COL B' adjust the
formula; =QUOTIENT - Numerator 'COL A' Denominator 10% of MAX value from
'COL A'

How can I write a formula to combine these two functions? I.e., formula in
'COL B' finds MAX in 'COL A' and divides all numbers by 10% of MAX value.

I can't set a static number as the denominator because the total point value
varies according to when the grades are calculated. So, what I usually do is
change the denominator in 'COL B' manually.

 

[Dave Hawley]

I could be way of base here, but try

=MAX(A1:A10)/MAX(A1:A10*0.1)

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[JE McGimpsey]

If I understand you correctly:

=B1/(MAX(A:A)/10)

 

[JEM]

Well, almost...

With 300 being the maximum value in COL A, using that formula returned the
following in COL B:

A>>>>>B
1: 100>>> 30 (should be 33)
2: 200>>> 15 (should be 66)
3: 300>>> 10 (should be 100)

It's upside down, or backwards or something but I'll be dipped if I can
figure it out...

Thanks, I'll keep at it!

 

[JE McGimpsey]

Nope, I didn't understand you:

Try: =A1/(MAX(A:A)/10)

and copy down. Format as a percentage.

 

[JEM]

Thanks, but it doesn't quite work...

One 'interesting' thing about both formulas posted here, they don't keep the
entire range of cells in the formulas.

row 1 has row 1-10
row 2 has rows 2-10
row 3 has rows 3-10

 

[JEM]

Hey! That one worked....WOW! Thanks.

But I have a question.

If I use =Y15/(MAX(Y:Y)*1) the formula looks for all numbers in the column.
But there is a number in the header that may excede the maximun points
earned, so it throws the formula off.

If I add cell reference numbers, for example I want the formula look in
cells 15-429, when I copy the formula, it changes the cell reference
numbers:

=Y15/(MAX(Y15:Y429)*1)

=Y16/(MAX(Y16:Y430)*1)

=Y17/(MAX(Y17:Y431)*1)



Can I get it to look like this? keeping the look-up for the cell references
the same for all cells, without including the info in the header.



=Y15/(MAX(Y15:Y429)*1)

=Y16/(MAX(Y15:Y429)*1)

=Y17/(MAX(Y15:Y429)*1)

 

[JE McGimpsey]

Use

=Y15/(MAX(Y$15:Y$429)/10)

Check out "The difference between relative and absolute references" in
XL help.

 

[JEM]

Thank you so much!!

I have close to 1000 students - Impossible to track them without Excel...
You just made my life a bit easier.