How to Assign Letter Grades in Excel
ฝัง
- เผยแพร่เมื่อ 3 ต.ค. 2024
- Join 400,000+ professionals in our courses: www.xelplus.co...
Calculating and assigning letter grades to students based on their achieved score is a common task. I'll show you how a super simple solution in Microsoft Excel to automate this task. Forget the Excel IF function for this. Instead use Excel VLOOKUP function. If you have office 365, you can use the XLOOKUP function as well.
🎬 LINK to more TH-cam Shorts videos: • Shorts (Tips, Tricks &...
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
📚 Excel RESOURCES I Recommend: www.xelplus.co...
🎥 GEAR I use: www.xelplus.co...
More resources on my Amazon page: www.amazon.com...
🚩Let’s connect on social:
Instagram: / lgharani
Twitter: / leilagharani
LinkedIn: / leilagharani
👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
The key here is non-exact matching in the VLOOKUP. Notice she didn’t include the last argument so it defaulted to true. This allows non-exact matching which assumes the lookup column is sorted in ascending order and matches the first row where the lookup value is greater.
Thanks for explaining it!
Very helpful
Agree I can't see FALSE OR 0 ( in accounting you need no be 100% true )
I thought the default was false for this? Or am I mistaken, I’ve never not written it actually, just thinking about it.
Using it
OMG all my life I have been using IF functions, which takes a while to set. Wish I knew this method from before. Leila you are a life saver!!!!
I appreciate these small examples but that 55 buggling me hard. ;)
Some sort of multiple choice that would increase the oddsof scoring something even if you don't know nothing. Just did a test with 67% pass grade because of this.
Totally
This trick was very useful instead of if function
Ctrl+E autofill is more exciting
This is is so easy if with if formula
Mam you earned me as subscriber beautifully explained
I agree that one should avoid doing tasks manually whenever possible. Just a note on your method; for those that do not want the extra columns containing the grading criteria to show, hiding columns will not impact the function. Alternatively, this can be accomplished with a nested IF statement. =IF(I2>=0.905,"A",IF(I2>=0.805,"B",IF(I2>=0.705,"C",IF(I2>=0.605,"D",IF(I2
There is an error in your bins. Both F and D contain value of 55
Yes typo error, but hope understood the scenario
Get it together Sid
😁
Excel with automation! Magic! Thanks Leila!🙂
THANK YOU! Your tutorial just helped me solved a huge problem.
Glad it helped!
She's amazing
Christopher Moltisanti 👌🏼👌🏼
Loved the show!
You could also try with switch, ifs and nested if
Advance ms office excel, you’ll learn more.. Appreciate this one too👍
I live this tip!
God bless you Leila!
Thank You. Learned 03 new things. F4, flash fill and the task itself
You can use =if(and( for the rank
Please send Sid Sloth to my office. That grade is outrageous.
He only got his name right on his test
It works because she's using the Approximation option of the VLOOKUP formula
which will assign a value if the number is higher than the lower bound of a category.
Works only on numbers and only if the score is ordered from lowest to highest.
True. If it's not ordered from lowest to highest, you can use XLOOKUP.
I´d use VBA
Great demo of xlooup thanks
Superb❤❤❤😊
Wow!!! I used 'if' function for this, which is very confusing. This video shows a way more simple formula.
Same with lookup let take colum of letters more freely
These videos make me so excited
Uao! Ctlr+E and F4 is actually what I learned in this video 😂
U inow I lvoe these videos because even though you may know how to do these this u can still learn a way to make that faster or quicker indeed
I knew vlookup but never knew this function.
Thanks a lot
Superb👍 thank you very much this video❤
Thanks a lot for this tips
Very helpful...thanks for sparing time to make this video
I really appreciate this. Learned this on my own. Thank you for explaining it.
I love you and the way teach
Ease way to do with IF formula
Excellent Tip...Thank you
Cool. Now set it up so it'll keep track of all the grades in a marking period and rank the students. One column should be the student number. Then the ranking can be posted in the classroom. We had a basic program to do this when I was in high school in the early 1980s. It ran on a HP2000.
👍
F4 to fix referencing will change my life.
Wow .. i didn't know ..🤯 will try .i am seriously loving this channel's youtube shorts ... so much to learn 💟
Thanks for the how to. It almost feels like you need to be a programmer to work excel.
I’m so glad to be subscribed to your channel.
Thanx
I follow your Every content. Also I will try. Thanks dear from Bangladesh.
This is awesome,thanks!
Very informative thank you
Of course Sheldon Cooper has A with score 99
Thanks for nice tip, it is great as always
Super tric Laila it newly from you only.
Great use of vlookup
Use conditional format
This is awesome!
Use array formula to fix the copy down.
Nice presentation
Thanks for sharing!
great lesson
Intervals: Text to Columns, and people should know the last (missing here) parameter of VLOOKUP function, which requires the proper ordering of the data and tells what value will be used in the case of an inaccurate match
She did Kim West dirty
You got a sub from me before. But this vid is gold!
Thank you, super useful. I found it only worked if the grade table was sorted ascending. (First try the grades were high-to-low and it didn't work)
Thanks a lot
VLOOKUP saves lives
Good to know! Thanks a lot!
I needed this
Thanks for the tip
Now this was useful
You're awesome ♥️
That's helpful. Thx
am also suprised you didnt use tables here rather than fixed refs, but love your stuff
Student: Teacher, why did you gave me an F?
Teacher: I was just following Leila’s Excel tips.
Amazing ❤
If there's a merged cells in score(F) then how you may solve this?🧐🧐🧐
Why don't you do vlookup with approx value instead ?
True instead of false???
No need for a helper column but the data must be sorted
Thanks
Many thanks!
spectacular!
Sheldon Cooper got the highest grade.
He has to :)
Lovely
Wait... Doesn't vlookup look for exact match? How does vlookup work if the value isn't in column 1??
Exactly! I am very curious to know how this works. It looks very useful but how does the function determine if I want lookup within a range or exact values?
If you notice, she didn't include the last criteria in the vlookup which is the "True" or "False" that determines the lookup based on exact value or the approximate value. I don't remember which is which but i guess by default it is set to approximate.
@@imserge9559 makes sense
@@imserge9559 @@imserge9559 been using vlookuo for over 18yrs and oddly never had to apply a result based on a range like grades in my profession but I'll try this out like in the example and see. Usually the last formula element is based on true/false, 1/0. If left blank I was thinking it would default to a #NA If no match. Again I'll check myself and test.
Nice
Why would I need to know this? Doing it manually works just fine
Thank you.
Sheldon Cooper, Mike Delfino, Gabrielle Solis - are you a fan of Big bang theory and desperate housewives? Anyone noticed more TV series characters?
Hint: Startrek, Sopranos, Seinfeld - the classics 😁
@@LeilaGharani nice, thanks for the hints ;) but with these series I’m not really familiar with. If it was up
to me I would include legen… wait for it …dary - Barney Stinson, Harvey Specter, Oberyn Martell, Kelly Bundy and Tom „Iceman” Kazansky ;)
All the best from Poland ! :)
@ssstudent123 I saw some from Knight Rider, Golden Girls & Roseanne. Don Johnson also snuck in there.
OOOOOOWWWWW THAAANKKKSSSSS
You can use if function. As well :D
Cp in excel
didn't work for me. all grades are F. any advice on where i might be wrong ?
While you were doing that I figured out all the grades I could see
👇
I don't understand how come you don't have to state condition C is higher than G, and it still works?
So if someone gets a 55 do they get a D or an F? Should it be 0-54? Do they just randomly choose if they get that score?
It'll be a D grade, because it's equal to the value in cell G3. The values in range F2:F6 are of type "text" and are not referenced in the vlookup-formula, so the don't matter to the result - only description for the user/teacher. However, you're right, that F2 should contain "0 - 54".
Is 55 a F or a D?
Thanks for sharing
How can I assign Gpa values instead of F C or D grades?
Instead of that you can also use "IFS" Function.
Second Data Range Should start with 56, isn't it?
Nice 🙂
The cover 😍
55
Ma'am, you got my grade wrong. I'm calling mom.
Amazing
Surprise to see the results, with true match no NA error how it is working
How can I change a "yes" or "no" answer to a "1" or "2"?