Quick Excel Lesson Please

Suppose I have two columns of grades, and I want to use them to calculate a grade as follows:

If the final is higher than the midterm, then the course grade is the final exam grade. Otherwise, the course grade is the average of the final exam and the midterm.

Is there a formula I can enter into Excel to do this?

-FrL-

MID=Grade on midterm
FIN=Grade on final

=IF(FIN>MID,FIN,AVERAGE(MID,FIN))
So, for example if midterm grades are in column A starting in Row 2 and Final grades are in Column B, Course grades could be calculated in column C with the formula:

=IF(B2>A2,B2,AVERAGE(A2:B2))

You’re using numeric grades, right?

Presuming you have the midterm in column A and the final in column B with the output in column C, you’d put =IF(B1>A1,B1,AVERAGE(A1,B1)).

On preview: Beaten. Darnit. Finally had an answer to an Excel problem, too.

Thanks, guys, I just figured it out, I used:

=IF(C2>B2, C2, (C2+B2)/2)

since I didn’t know about “AVERAGE” but what works works.

Can I nest if statements? Something like this?:

=if(C1=100, “A+”, =if(C1>=93, “A”, =if(C1>=90, “A-”, “F”)))

?

(That example pretends any grade less than a ninety is an F, which of course isn’t what I would actually do! It’s just for the purpose of the example.)

Okay, I think that’s working if I take the “=” out of the nested ifs. If someone could confirm or disconfirm that it should work (so I can be sure I’m not misinterpreting what I’m seeing) I would appreciate that.

Or if there’s a much easier way than the formula I’m using, that would be appreciated as well.

-FrL-

How about just =MAX(C2, AVERAGE(B2, C2))?

Here’s a formula. Right afterwards I’ll tell you exactly which part is salient, so no need to read through it immediately.

For some reason, when I hit enter after putting in that formula, it says there is an error, and highlights the word “if” coming right after the expression ’ “C” '. I can’t see why it has a problem with that “if” if it doesn’t have a problem with any of the “ifs” that come before. Is there a length (or nesting) limit for formulas?

Thanks alot for any help or comments!

-FrL-

For those watching at home:

The solutions above used IF statements—codespeak for “if…then.” You may see them in other applications (e.g., SQL, PHP) as IFF or some other variant—they tend to work on the same principles. Here’s how they (generally) work:

There are four sections to the statement. “IF( …) starts it off and tells the cell that it needs to check for a condition. There are three sections inside the parenthesis (sometimes two). Commas tell the function when you’ve come to the end of a section (if a comma is part of a section, some languages allow you to “escape” that comma so it doesn’t get treated as a section break).

The first section tells the function what to test the condition on. In Laughing Lagomorph’s solution, FIN>MID says to point to the cell with the final grade and see if it’s greater than the midterm grade. Hence, his(?) B2>A2 assumed the final grade was in cell B2 and the midterm grade was in A2. You can set up all sorts of calculations and whatnot in the condition. In general, the formula you put in the first section will either be true or false (i.e., if the final grade is higher than the midterm, the expression is true, if it’s lower than the midterm grade, it’s false.

The second and third sections tell Excel what to do. The second section tells Excel what to do if the statement is true. Since the OP wanted the final grade to be the course grade if the condition was true, then merely referring to the cell (B2) accomplished that. Note that in this (and the third) section, you can put in anything you like, from a text string “keep the final grade” to another formula or even another IF() statement.

The thirds section is simply what you want the function to do if the condition is false. In this case, it takes the average of the appropriate cells. Again, this can be anything you want it to be. Some conventions allow you to skip the third section. In that case, it will only do what you tell it to do in the second section, or else it will do either nothing or move on to the next programming line.

Things can get a bit more complicated, say with an if…then…else, or by nesting if statements inside of the sections. You can start with the simple “if it is raining, then we will go to the museum, else we’ll go to the beach.” (Three sections, a condition, what happens when the condition is true, and what happens if the condition is false.) And add and add and add. “If it is raining, then if the museum is open and if we have the money, we’ll go to the museum, else we’ll go to an ATM to get cash…). These are fairly simple to build, the thing to be careful with is watching the parenthesis and comma placement so you keep track of what conditions are being evaluated at the time.

I have no idea why I was compelled to write all this out. Perhaps it’s because I search the boards all the time and sometimes want to know what’s going on in some of the technical threads. I hope I haven’t added confusion or mucked up the explanation!

Rhythm

Thanks for doing that, though. It assuages a bit of guilt I was feeling over just the issue you’re addressing.

I just looked at the MS Excel Help, and it says,

So you’ll need to rewrite the formula so that it doesn’t nest so deeply.

Yep.

Sorry I can’t help with the OP anymore, I screw up the nesting aspect every time myself.

A VLOOKUP to a score-grade table will probably give you what you need.

Oops, sorry, I’m so used to finding Help so unHelpful for things like this I didn’t even think to look.

-FrL-

Yes, I understand that. But, in this case, by searching on “nesting” you get a useful response.

What would be nice, however, would to be told that excessive nesting was the problem in the error message.

Okay, thanks for the tutorial everyone.

I tried VLOOKUP but hit a snag when I couldn’t figure out how to put a cell name into a space where VLOOKUP wants a value. I think I will just use the seven-nesting version and just manually change everything assigned a “C or Below” to its correct letter grade.

Again, thanks!

-FrL-

=IF(C2>B2, C2, (C2+B2)/2)

That was working this morning. Now it’s giving me “###########”. What’s that mean?

-FrL-

It can just mean the cell isn’t wide enough to display the result as it is currently formatted.

Try widening the cell, or changing the format (for instance, to remove unnecessary decimal places).