The Right Tool For the Job

[ralphmelton]

Two incidents described under this title:

- On Sunday, we were making a double recipe of a Lentil-Barley-Vegetable Skillet for Sunday dinner. I decided to use our big six-quart frying pan, that we bought in the summer and hadn't yet used. When the food nearly filled the pan, I felt an inordinate pleasure that I'd used that pan and not a smaller one.

- Lori needed to average grades last night. For months I'd been suggesting that she should use a spreadsheet for this. She'd demurred, believing that a spreadsheet couldn't handle her needs of not penalizing certain kids for grades that they hadn't been able to receive.
I finally sat down to look at the problem, and it turned out to be easy; Excel provides a SUMIF() function that trivially does the 'sum the elements of those cells that correspond to the cells in this range that meet a certain criterion'. It saved Lori a lot of calculation time.

Comments

[glenbarnett.livejournal.com]

There are actually several ways to tackle it, but SUMIF is a good one.

[ralphmelton.livejournal.com]

What other ways to handle it that simply are there? I had plans for hacking my through, but SUMIF seemed most straightforward.

[glenbarnett.livejournal.com]

Oh, I don't necessarily guarantee it will be exactly "that simply" - it depends on exactly what you're trying to achieve as to whether the other ways are any better. But there are other ways.

[Well, 'hacking your way through it' is certainly another way to do it (e.g. writing some visual basic code), but I agree that's obviously less simple.]

Remember that I'm working with less than full information here (I'm unfamiliar with US school grading traditions), but let me make some guesses about the precise circumstances (and be prepared for me to slightly modify my answers depending on how close my guesses are).

I assume that there are several pieces of assessment. For each student on each piece of assessment there is a numerical 'grade' (if it's not a number, you can't "average" it in the usual sense of average), or a missing value (where the student didn't receive a grade that they weren't to be penalized for), which I'll assume is a blank cell for now.

I've further assumed that the individual assessments are already scaled to be out of their fraction of the total assessment (so if someone has done everything you just sum their marks to get an overall numerical grade):

Like so:

	 C 	 D	 E	 F	 G
 7	        Item1	Item2	Item3	Total
 8	Out of:	20	50	30	100
 9	 
10	
11	Bazza	15	46	23	84
12	Kazza		35	26	
13	Shazza	14	28		
14	Gazza	0	18	32	50
15	Mike	18		27	

So now one further assumption. Let's look at Kazza. I assume you want to give her a numerical grade of ((35+26)/(50+30))*100. Then, as you say, a good approach is to use SUMIF on both the numerator and denominator in that fraction.

Here are a couple of other tools that can be useful if the situation is slightly different:

{=SUM(IF(ISNUMBER(D12:F12), D12:F12,""))}
Note the {} - this is an array formula. Type the formula as usual and then, instead of pressing enter, press ctrl-shift-enter (or if you're like me you already pressed enter - just hit F2, then ctrl-shift-enter).

This pretty much works like sumif above, but you can do some other
fancy stuff in there also. Note also that there are a whole variety of logical functions that can go in the IF there...

For example, let's say that you want to not give them zero for a missed assessment, but you also don't want to give their full average either. You can adapt the above give some particular numerical value instead (like some 'minimal passing grade', which for each assessment could be recorded in a row below the Item totals, as shown below).

	 C 	 D	 E	 F	 G
 7	        Item1	Item2	Item3	Total
 8	Out of:	20	50	30	100
 9	MinPass	10	30	12	100
10	
11	Bazza	15	46	23	84
12	Kazza		35	26	
13	Shazza	14	28		
14	Gazza	0	18	32	50
15	Mike	18		27	

The second option in the "if" above could then give the students the minimum pass for the missed assessments, for example. Or give them 90% of their average on the other assessments. Or...

There's piles of other stuff you can do.

If you just want to do a straight average of the item grades, and the missed assessments you don't want to penalize for are recorded as 0:
{=AVERAGE(IF(D12:F12<>0, D12:F12,""))}

Here's a formula for weighted averages (item grades in column A and weights in column B)

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

If all grades were present, you could use that approach.

Now if you combine Sumproduct, If, and the array formula thing, you can do what you did with sumif, but it's easy to adapt it to some other fancier stuff as well, like we saw above.

It sort of depends on exactly the effect you want to achieve, but in Excel there's usually several ways to achieve a given effect (I can put the things I've mentioned together in several different ways to do what sumif can do). In a particular circumstance one will be easier, but change the situation just a little, and you'll want one of the other tools...

By the way, there are still more ways to tackle this sort of thing in Excel.

[glenbarnett.livejournal.com]

Hmm, ignore that extraneous "100" at the end of the "MinPass"
column.

[glenbarnett.livejournal.com]

Oh, and the sumif is really only required on the denominator. The numerator will sum just fine of course. I even realised that when I typed it but for some reason didn't go back and edit it before I posted.

[glenbarnett.livejournal.com]

Forgot to say: Lentil-Barley-Vegetable Skillet sounds yummy.

What's in it? Roughly, how does it go? (Just a basic order of operations will do fine, I don't need a super-detailed recipe. Oh, and no hurry)