# Quick Excel Lesson Please
**URL:** https://boards.straightdope.com/t/quick-excel-lesson-please/453133
**Category:** Factual Questions
**Created:** [June 16, 2008, 5:57pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133 "2008-06-16T17:57:45Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 5:57pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/1 "2008-06-16T17:57:45Z")
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-
---
### Author:  [@Laughing\_Lagomorph](https://boards.straightdope.com/u/Laughing_Lagomorph)
#### Post date: [June 16, 2008, 6:06pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/2 "2008-06-16T18:06:44Z")
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))
---
### Author:  [@Bosstone](https://boards.straightdope.com/u/Bosstone)
#### Post date: [June 16, 2008, 6:08pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/3 "2008-06-16T18:08:39Z")
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.
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 6:16pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/4 "2008-06-16T18:16:52Z")
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.)
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 6:20pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/5 "2008-06-16T18:20:24Z")
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-
---
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [June 16, 2008, 6:23pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/6 "2008-06-16T18:23:02Z")
How about just =MAX(C2, AVERAGE(B2, C2))?
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 6:30pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/7 "2008-06-16T18:30:54Z")
Here’s a formula. Right afterwards I’ll tell you exactly which part is salient, so no need to read through it immediately.
> [@](#):
>
> =IF(D2=100, “A+”, IF(D2\>=93, “A”, IF(D2\>=90, “A-”, if(d2\>=87, “B+”, if(d2\>=83, “B”, if(d2\>=80, “B-”, if(d2\>=77, “C+”, if(d2\>=73, “C”, if(d2\>=70, “C-”, if(d2\>=67, “D+”, if(d2\>=63, “D”, if(d2\>=60, “D-”, “F”))))))))))))
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-
---
### Author:  [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)
#### Post date: [June 16, 2008, 6:50pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/8 "2008-06-16T18:50:54Z")
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
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 6:54pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/9 "2008-06-16T18:54:59Z")
[QUOTE=Rhythmdvl]
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
[/QUOTE]
Thanks for doing that, though. It assuages a bit of guilt I was feeling over just the issue you’re addressing.
---
### Author:  [@Giles](https://boards.straightdope.com/u/Giles)
#### Post date: [June 16, 2008, 7:03pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/10 "2008-06-16T19:03:16Z")
[QUOTE=Frylock]
Is there a length (or nesting) limit for formulas?
Thanks alot for any help or comments!
-FrL-
[/QUOTE]
I just looked at the MS Excel Help, and it says,
> [@](#):
>
> Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on.
So you’ll need to rewrite the formula so that it doesn’t nest so deeply.
---
### Author:  [@Laughing\_Lagomorph](https://boards.straightdope.com/u/Laughing_Lagomorph)
#### Post date: [June 16, 2008, 7:26pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/11 "2008-06-16T19:26:59Z")
[QUOTE=Rhythmdvl]
…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(?)…
[/QUOTE]
Yep.
Sorry I can’t help with the OP anymore, I screw up the nesting aspect every time myself.
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [June 16, 2008, 7:28pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/12 "2008-06-16T19:28:35Z")
[QUOTE=Giles]
So you’ll need to rewrite the formula so that it doesn’t nest so deeply.
[/QUOTE]
A VLOOKUP to a score-grade table will probably give you what you need.
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 7:53pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/13 "2008-06-16T19:53:43Z")
[QUOTE=Giles]
I just looked at the MS Excel Help,
[/quote]
Oops, sorry, I’m so used to finding Help so unHelpful for things like this I didn’t even think to look.
-FrL-
---
### Author:  [@Giles](https://boards.straightdope.com/u/Giles)
#### Post date: [June 16, 2008, 7:56pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/14 "2008-06-16T19:56:06Z")
[QUOTE=Frylock]
Oops, sorry, I’m so used to finding Help so unHelpful for things like this I didn’t even think to look.
-FrL-
[/QUOTE]
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.
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 8:28pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/15 "2008-06-16T20:28:53Z")
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-
---
### Author:  [@Frylock](https://boards.straightdope.com/u/Frylock)
#### Post date: [June 16, 2008, 9:36pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/16 "2008-06-16T21:36:20Z")
=IF(C2\>B2, C2, (C2+B2)/2)
That was working this morning. Now it’s giving me “###########”. What’s that mean?
-FrL-
---
### Author:  [@Laughing\_Lagomorph](https://boards.straightdope.com/u/Laughing_Lagomorph)
#### Post date: [June 16, 2008, 9:48pm UTC](https://boards.straightdope.com/t/quick-excel-lesson-please/453133/17 "2008-06-16T21:48:33Z")
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).