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.
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.
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
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
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.
@@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 ! :)
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
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.
These have been so helpful. I haven’t had a chance to watch them all is there one on how to make a spreadsheet that says moves a whole row up if say a column with a date is changed. So that the spreadsheet is sorted by the next date?
Dear Leila, adore your videos and continue to study with them, would kindly ask how you would create a nice chart found in internet but can not add here that image(
Hardly anyone appreciates the approximate match feature of Vlookup. We the Tax people use it more frequently to get tax amount which is based on slabs like this.
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".
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
Thank You. Learned 03 new things. F4, flash fill and the task itself
There is an error in your bins. Both F and D contain value of 55
Yes typo error, but hope understood the scenario
I really appreciate this. Learned this on my own. Thank you for explaining it.
Excel with automation! Magic! Thanks Leila!🙂
Student: Teacher, why did you gave me an F?
Teacher: I was just following Leila’s Excel tips.
You could also try with switch, ifs and nested if
Very helpful...thanks for sparing time to make this video
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
This trick was very useful instead of if function
Please send Sid Sloth to my office. That grade is outrageous.
He only got his name right on his test
God bless you Leila!
Advance ms office excel, you’ll learn more.. Appreciate this one too👍
I live this tip!
Great demo of xlooup thanks
Superb👍 thank you very much this video❤
Excellent Tip...Thank you
This is is so easy if with if formula
Same with lookup let take colum of letters more freely
Wow .. i didn't know ..🤯 will try .i am seriously loving this channel's youtube shorts ... so much to learn 💟
She's amazing
You can use =if(and( for the rank
These videos make me so excited
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
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)
THANK YOU! Your tutorial just helped me solved a huge problem.
Glad it helped!
Wow!!! I used 'if' function for this, which is very confusing. This video shows a way more simple formula.
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’m so glad to be subscribed to your channel.
Thanks a lot for this tips
I follow your Every content. Also I will try. Thanks dear from Bangladesh.
Very informative thank you
This is awesome,thanks!
I love you and the way teach
Thanks for sharing!
Superb❤❤❤😊
I knew vlookup but never knew this function.
Thanks a lot
This is awesome!
Thanks for the how to. It almost feels like you need to be a programmer to work excel.
Good to know! Thanks a lot!
great lesson
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.
Nice presentation
Thanks for the tip
Great use of vlookup
You're awesome ♥️
That's helpful. Thx
am also suprised you didnt use tables here rather than fixed refs, but love your stuff
You got a sub from me before. But this vid is gold!
Of course Sheldon Cooper has A with score 99
Thanks for nice tip, it is great as always
Thanks a lot
Amazing ❤
Christopher Moltisanti 👌🏼👌🏼
Loved the show!
spectacular!
I needed this
Ease way to do with IF formula
Thanx
Thank you.
Now this was useful
Ctrl+E autofill is more exciting
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
Thanks for sharing
F4 to fix referencing will change my life.
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.
👍
Great! 🤩
Super tric Laila it newly from you only.
Lovely
Use array formula to fix the copy down.
Fantastic..
I´d use VBA
Amazing
Nice
Nice 🙂
I don't understand how come you don't have to state condition C is higher than G, and it still works?
She did Kim West dirty
You can use if function. As well :D
Ma'am, you got my grade wrong. I'm calling mom.
OOOOOOWWWWW THAAANKKKSSSSS
Great
Good & simple demo nice, how to form if comments pls. Espicialy time chart.
Get it together Sid
😁
These have been so helpful. I haven’t had a chance to watch them all is there one on how to make a spreadsheet that says moves a whole row up if say a column with a date is changed. So that the spreadsheet is sorted by the next date?
Thanks
Many thanks!
I love you, i like your videos 💋💋
The cover 😍
thanks, i use a nested of on tha
Dear Leila, adore your videos and continue to study with them, would kindly ask how you would create a nice chart found in internet but can not add here that image(
Hardly anyone appreciates the approximate match feature of Vlookup. We the Tax people use it more frequently to get tax amount which is based on slabs like this.
Use conditional format
If there's a merged cells in score(F) then how you may solve this?🧐🧐🧐
Instead of that you can also use "IFS" Function.
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
Cp in excel
Why would I need to know this? Doing it manually works just fine
0:01
How did Sid mess up that bad 💀
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".