IF Function Blues

Posted by Geoff on December 20, 2000 9:23 PM

My students were given the option to make the lowest of
their four tests worth 15% and the final worth 25%. The
remaining 20% is based on homework and will not change.

I entered a "yes" for the option as 1, a "no" as a 0.
The last time I programmed anything was when BASIC was
the hottest thing around.

How do I write an IF statement that allows me to do
this?

I have for a NO to the option:
=IF(AK30=0, SUM(AB30+(AG*0.2)+(AH*0.2)+(AI*0.2)+(AJ*0.2)),

But I'm stuck on how to weight the MIN (which will obviously shift for each student)
*.15, the Final*.25, and the other two test scores *.2

Any ideas?

Thanks

Geoff

Posted by Celia on December 21, 2000 12:02 AM

=IF(AK30=1,SUM(AB30,SUM(AG30:AJ30)*0.2,MIN(AG30:AJ30)*-0.05,MAX(AG30:AJ30)*0.05))

Posted by Aladin Akyurek on December 21, 2000 12:25 AM

I assume that AB30 contains the score for homework and AJ the Final.

=IF(AK30=0,AB30+SUM(AG:AJ)*0.2),AB30+SUM(AG:AI)*0.2-MIN(AG:AI)*0.05+AJ*0.25)

Aladin

Posted by Aladin Akyurek on December 21, 2000 12:32 AM

Celia: You'll probably right about the "Final" being the MAX of AG30:AJ30.

Posted by Aladin Akyurek on December 21, 2000 1:45 AM

Just curious: Why ask the student how s/he wants her/his score be computed, instead of assigning the best outcome according the rules you specified?

The best outcome then would be (using Celia's formula):

=MAX(SUM(AB30,SUM(AG30:AJ30)*0.2,MIN(AG30:AJ30)*-0.05,MAX(AG30:AJ30)*0.05))

Aladin

Posted by Tim Francis-Wright on December 21, 2000 6:48 AM

Just curious: Why ask the student how s/he wants her/his score be computed, instead of assigning the best outcome according the rules you specified? The best outcome then would be (using Celia's formula): =MAX(SUM(AB30,SUM(AG30:AJ30)*0.2,MIN(AG30:AJ30)*-0.05,MAX(AG30:AJ30)*0.05)) Aladin

Actually, the final doesn't have to be the maximum
of the other scores:

I've assumed homework in AB, AG-AI are tests,
AJ is the final, and AK is the switch:

1. Student's choice is binding:
=IF(AK30=1,(AB30*.2)+SUM(AG30:AI30)*.2-MIN(AG30:AI30)*.05+AJ30*.25,AB30*.2+SUM(AG30:AJ30))

2. Excel makes best choice:
=(AB30*.2)+SUM(AG30:AI30)*.2+max(AJ30*.25-MIN(AG30:AI30)*.05,AJ30*.2)

Good luck!

Posted by Geoff on December 21, 2000 8:54 AM

I GOT IT

Okay, first of all, thanks for all your suggestions
It's nice to know that 1) There are people out there
who are much smarter than I am and 2) They're helpful,
too.

I was able to combine a couple of your suggestions,
which led me to the answer.

If anyone is interested, here's the formula I came
up with in the end:

=IF(AK30=1,AB30+SUM(AG30:AI30)*0.2-MIN(AG30:AI30)*0.05+AJ30*0.25,AB30+SUM(AG30:AJ30)*0.2)