Create a formula

[Lei]

Hi,

I'm new to Excel's formula. Here is my problem:
I have an Excel Spreadsheets with students grades. each
student have thirteen exams, but I only need the 12 of
them with greater grades( I want to get rid of the lowest
grade). if each of the twelve grade is greater than 60,
then I give him/her point of 1.5 instead, otherwise, get
0 point. finally I need to add the point together for
each student.
Does anyone can help me on how to create a formula for my
problem?
Thanks very much!

 

[Earl Kiosterud]

Lei,

This is for where the grades are in A2:A14. Not very straightforward for a
first-time formula example, I fear. It works by finding those greater than
60 (A2:A14>60), which resolves to an array of TRUE or FALSE values. It
coerces those to 1 or 0 by multiplying them by 1. The SUMPRODUCT adds all
those 1's up. Then MIN ensures that it doesn't go over 12. The final
result is multiplied by 1.5

=MIN(SUMPRODUCT((A2:A14>60)*1),12)*1.5

Or you may prefer this:

=MIN(SUM(IF(A2:A14>60,1.5,0)),18)

This must be entered as an array formula (Ctrl-Shift-Enter whenever entering
or editing the formula), because of the array (A2:A14) in it. It uses a
more straightforward IF funtion, adding up the resultant 1.5 values (SUM),
then taking the minimum of that total, or 18.

 

[Lei]

Earl,

Thank you very much!
Lei

-----Original Message-----
Lei,

This is for where the grades are in A2:A14. Not very straightforward for a
first-time formula example, I fear. It works by finding those greater than
60 (A2:A14>60), which resolves to an array of TRUE or FALSE values. It
coerces those to 1 or 0 by multiplying them by 1. The SUMPRODUCT adds all
those 1's up. Then MIN ensures that it doesn't go over 12. The final
result is multiplied by 1.5

=MIN(SUMPRODUCT((A2:A14>60)*1),12)*1.5

Or you may prefer this:

=MIN(SUM(IF(A2:A14>60,1.5,0)),18)

This must be entered as an array formula (Ctrl-Shift- Enter whenever entering
or editing the formula), because of the array (A2:A14) in it. It uses a
more straightforward IF funtion, adding up the resultant 1.5 values (SUM),
then taking the minimum of that total, or 18.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------




.

 

[tsides]

I just learned something new called an "array formula" which will help
you.

If your data looks like this:
Grade1 Grade2 Grade3... Grade13
Student1
Student2
Student3

Then you call go to cell O2 and type the following formula:
=COUNTIF(B2:N2,">60")*1.5
and copy it all the way down column O for all the students.

For any students who never got a score that was 60 or below, the avlue
in column O is 1.5 too high. In cell P2, enter the following formula:
=IF(MIN(B2:N2)>60,O2-1.5,O2)
and copy it all the way down column O for all the students.

This will subtract 1.5 from your final rating if the lowest (minimum)
score for the student was more than 60. The final point total for
each student is now in column P.

 

[tsides]

If your data looks like this:

Test1 Test2 Test3... Test13
Student1
Student2
Student3

Then enter the following formula in cell O2:
=COUNTIF(B2:N2,">60")*1.5
and copy it down the column for all students.

Now, cell O2 is 1.5 too high if a student never received a grade that
was 60 or less. Therefore, in cell P2, enter the following formula:
=IF(MIN(B2:N2)>60,O2-1.5,O2)
and copy it down the column for all students.

Column P now contains the final point totals.

 

[Earl Kiosterud]

Tsides COUNTIF is a better solution than my SUMPRODUCT. I have a bad neuron
where COUNTIF is stored, I suspect.

A final formula could be:

=MIN(COUNTIF(B2:N2,">60"),12)*1.5

 

[tsides]

I love it! the MIN gives you no more than 12 grades * 1.5 and
therefore no need for a second column to remove the 1.5 for the 13th
grade! Wow. Good solution.

 

[Earl Kiosterud]

Tsides,

Actually, the two formulas in your solution can in fact be combined. If O2
contains:
=COUNTIF(B2:N2,">60")*1.5

Then that can be put into this formula:
=IF(MIN(B2:N2)>60,O2-1.5,O2)

wherever there's a reference to O2, in place of it:
=IF(MIN(B2:N2)>60,COUNTIF(B2:N2,">60")*1.5-1.5,COUNTIF(B2:N2,">60")*1.5)

You can easily paste one into the other (which I did here, and didn't
actually test in a worksheet).

It can, though, make for messy formulas, and it's actually good practice to
keep them separate. Less errors and easier maintenance. And you can easily
see the intermediate results. The most concise is often confused with the
best solution from a practical standpoint where errors, debugging, changes,
etc., are involved.