There is always something new to learn here. I didn't know that there is something called 'Ifs' function and I always used the nested if. Now I can use this new formula
Thanks a lot dear. In fact I just posted my comment under a video of another excel guru as to how can we do it. Really it's wonderful. Watching all yr presentations. Need a video from you on LET and Lambda.
Awesome, thanks a lots.. You know Chandoo, I learnt power query from you and I also learnt Power BI. Now I am looking the job as Power BI developer to be a Data Analyst. The excel tips are very helpful to me and thanks for it. Could you please make videos about DAX? That how to use DAX functions in Power BI to next level? Thanks again to make me a good data analyst.😊😊😊
Why not use text function + vlookup to find who will drive batmobile 🤷♂ Text function to convert date into text then find it in the table using vlookup. Ps :- love you learning a lot from your channel since last 1 year. 😍
Great suggestion! You can use that too. As I wanted to showcase IF, IFS & CHOOSE, I went with the CHOOSE example first, but the bonus trick shows a better way of doing it too. PS: Thanks for learning and becoming awesome 😎
Hi Chandoo, You have explained inside x lookup how to use two Datasets, it is amazing thing🙏🙏 I want to Your help, in my current role, I am facing lack of store produce issue, please tell me, where will I learn more Advanced Store produce ? Swift response
Nice vid- for the batmobile example- how does the chart on the left- RELATE to the chart on the right- where is the connection to the" weekday/ person" chart in the formula?
can you share,please, an example of how formula can result showing "months" number value when A1 - is a date of start and B1 - is today's date....thank you! 🙃👍
Thank you for this video. Pls I need some help. I can't use F4 to change my references to absolute. I'm using a windows computer pls someone should help me
Hi. I have a students' table with Mark. What I want. I need to check the top 2 people based on the particular subject. Can you do some videos for this?
One question which is not related to IF function. I have a big database comprising several columns and rows. From this I can find the highest value using MAX function. But how can I find out the cell address of that cell containing the highest value. Please help.
@@chandoo_ Yes sir been doing that all along…thank you for the videos 👍.What I meant to ask was I try solving your file before looking up the video or a snippet of the video…So that I can gauge my knowledge so I don’t view the code written..but as you said it’s easy to just delete and start.😊
The one I keep finding myself needing is a way to solve the following: if(_BIG_LONG_FORMULA_,_BIG_LONG_FORMULA_,_SOMETHING_ELSE_) When the big long formula is the same in both places, what I really want is not to have the big long formula typed out twice because it makes the whole thing almost unreadable (I'm talking formulas with multiple nested Ifs and lookups). I feel like there has to be a better way, I just can't seem to find it.
You can use LET(). For example =LET(x, BIG_LONG_FORMULA, IF(x, x, something_else)) Also, there are some clever ways to avoid IF. I suggest reading www.excelhero.com/blog/2010/01/i-heart-if.html
@@chandoo_ I think "LET" may be exactly what I'm looking for, thanks! I'll give you an example of the type of thing I'm trying to model, I have a spreadsheet for keeping track of payroll, but the union contract is insanely complex for the rules to figure it all out, for example, there are shift premiums for working between 1500 and 2300, a higher premium for between 2300 and 0700, and another premium that stacks on top of those if it's between 1500 on a friday and 0700 on a Monday. Additionally all those premiums only apply if more than 2 consecutive hours are worked in the appropriate timeframe, but if more than half of a shift is in a specific timeframe, all of the shift is counted at that rate, unless a higher rate is hit. But if the employee is in overtime all premiums kick in immediately instead of only if more than 2 hours are worked. And let's not even get started on stat-holidays, and super-stat-holidays! You can imagine that the formula ends up being super complex to capture all of that!
@@chandoo_ That's what I do, but even so, there's such a dependency on each other cell that it can be hard. Basically I calculate total hours, overtime hours, evening hours, night hours, and weekend hours, then total dollar value.
Perfect length and perfect tips. Exactly as per my taste. I dont use if alot since xlookup, however learnt a lot rearding choose and weekday! Thanks
You are welcome Tine 😀
👍 great Chandoo. Knowing function is not important but knowing where to use is important and you taught us the second way. Thanks again big brother.
My pleasure Saleh.
There is always something new to learn here. I didn't know that there is something called 'Ifs' function and I always used the nested if. Now I can use this new formula
Your method of teaching is simpler. Well Done!
Hey Chanddoo. you dont figure in those lists ....but you are a Super Hero of Excel. When they build that wall of fame ........ youll be there
Thanks a lot Chandoo for sharing such an amazing video. Another versatile use of if function!
Most welcome!
Hi Chandoo,
I enhance my knowledge.
"THANK YOU"
Regards
You are welcome Mohammed. Please share what you are learning with others and help colleagues in need.
@@chandoo_ ofcourse I do.
As always you are awesome & make concepts simplified, Thank you!
Glad you like them!
Never realised about the black end bracket! Thank you!!
Superb sir...... Very good examples, and brief too, thanks
You are most welcome
IFS & two Xlookup function liked most... 👌, Great work sir,,... Both function will help me sort out daily work in office....🙏🙏
Awesome to hear that Suraj.
👏👏👏 I'm so happy I found your channel! You're a good teacher.👍
Thanks for your teaching method
Thanks chandoo you made my day
Awesome video Chandoo
Excellent....good to see your pivot table advice.
Sir, you had my bow.
You are really awesome teacher of Excel.
Very nice to have this CHOOSE!!! Thank you Chandoo!!!!
Glad you like it!
IFS was a take away !! thanks..
😎
I love you clips. Thank you sir!
Great video. Thanks for all the tips.
Thanks!
You are welcome :)
Very clear, excellent content!
very helpful video - ifs and choose is new and useful for me. many thanks
Awesome information and tips. Thank you
very well explained with good examples. Worth watching. Thanks
Thank you Chandoo :)
Thanks chandoo sir
Instead of writing a long if function for grading we can also use vlookup by keeping it as a approximately match.
We can. As the topic is about IF, I stayed with IF based examples.
Amazing stuff! Thank you so much.
Bell notification on only for you brother ✌️
Gracias por compartir los tips. Eres un grande 💯
tnx a lot
cool that index/choose tip and the xlookup thanks alot
You are welcome Mahmoud.
You are awesome.
Thank you sir 🙏
You are welcome TD.
You are a owesome human being.🥳
Chandoo, another outstanding video, thanks!
Glad you enjoyed it Chris.
Great video Chandoo! I learnt a few new tricks to use with IF and thanks a lot for that! 😊👍
Great to hear that Vijay.
Good afternoon sir, Thankful for the important video
Good day Aravind and you are welcome :)
Thank you, Chandu. That's brilliant.
You are welcome Sairam.
Thanks 😊 for this great using tip of "if not found" option in Xlookup function.
Glad it was helpful!
You make learning so easy and impressive...
Thank you!
Thanks a lot dear. In fact I just posted my comment under a video of another excel guru as to how can we do it. Really it's wonderful. Watching all yr presentations. Need a video from you on LET and Lambda.
I have a video on LAMBDA here. studio.ua-cam.com/users/videoYrMCkE59ef4/edit Check it out
Thank you sir
You are welcome Manikandan.
Haaa super tips❤️😘😘❤️👍
I'm trying to learn data science from your content.
Thanks sir 🙏
You are welcome Karan.
Man! You are still the best!!!
Your videos are makes me excel pro
YAY!!!
Good video.
Glad you enjoyed it
Good concept especially the last one.
You are welcome Radhe.
Awesome, thanks a lots..
You know Chandoo, I learnt power query from you and I also learnt Power BI. Now I am looking the job as Power BI developer to be a Data Analyst.
The excel tips are very helpful to me and thanks for it.
Could you please make videos about DAX? That how to use DAX functions in Power BI to next level?
Thanks again to make me a good data analyst.😊😊😊
Excellent👍
V well explained thanks
Thank you Sir...
Thanx
You are really awesome chandu...,short and crispy videos makes easy to understand...
Thank you so much 🙂
Salaam chandoo Bhai,,,
Xlookup has covered mostly formulas so everyone should learn xlookup 😊
Nested Xlookups? 🤯🥳
Thanks 🙂
It helps a lot..
You are welcome DI.
Chandoo, the excel Super Hero.
🦸♂️ We all are...
Very Nice
Why not use text function + vlookup to find who will drive batmobile 🤷♂
Text function to convert date into text then find it in the table using vlookup.
Ps :- love you learning a lot from your channel since last 1 year. 😍
Great suggestion! You can use that too. As I wanted to showcase IF, IFS & CHOOSE, I went with the CHOOSE example first, but the bonus trick shows a better way of doing it too.
PS: Thanks for learning and becoming awesome 😎
If formula se nikli gayi value ka sum kr sakte ya nhi please guide sir
Hi Chandoo,
You have explained inside x lookup how to use two Datasets, it is amazing thing🙏🙏
I want to Your help, in my current role, I am facing lack of store produce issue, please tell me, where will I learn more Advanced Store produce ? Swift response
Nice vid- for the batmobile example- how does the chart on the left- RELATE to the chart on the right- where is the connection to the" weekday/ person" chart in the formula?
Basic to intermediate PowerPoint lesson or series.
Please Sir
in the tip #4 how does the index know that fro a Wednesday, Robin will be driving? We haven't matched it to a value yet it knows
Instead of Choose, text function with Xlookup is better alternative
If not found can be good alternative for second version of INDEX.
Hi sir,
Can I know which video editing software u r using ?
Will be a great help!
I use Camtasia for creating and editing my videos. You can get a free trial from here - techsmith.pxf.io/c/3169105/481530/5161
@@chandoo_ I would like to thank you for taking out your time and replying me.
Thank you so much...
Great I saw many videos but everyone inform basic tips only
You are welcome Supriya.
❤❤❤
HI, great tips. I have question related to using if formula when #N/A is also a condition and I have many #N/A to see. How can I use it?
How to use for value and max values xlookup
I need this video in Brother
interesting! But in your examples you typed , in stead of ; That might be a version error? I only got it to work with the ; 🙂
Thank you. In European version of Excel ; is used and almost everywhere else , is used
I don't have the xlookup function is there an alternative function to use?
Do "and" ,"or" multiple conditions in single formula of if
Please see the video I mention at the end. It covers those topics - ua-cam.com/video/-yFpzIRifK4/v-deo.html
As always you are nice. Please make video on Microsoft Office Specialist: Associate" Certifications MO-200, MO-201. Are they really worth ?
Thanks Ankit. I will share my views on certifications in an upcoming video.
🔥🔥🔥
😎
can you share,please, an example of how formula can result showing "months" number value when A1 - is a date of start and B1 - is today's date....thank you! 🙃👍
Your content is very helpful . Would love to know if you have a patreon account?
Switch function also can you
X lookup or Vlookup with approximate match is Better than nested if
Hey chandoo
Thanks for the video.
Chandoo, i want to learn VBA codeing. Do you have paid course for that.
Yes I do. Please see chandoo.org/wp/vba-classes/
Thank you for this video. Pls I need some help. I can't use F4 to change my references to absolute. I'm using a windows computer pls someone should help me
👍
Sound at 7:08 😂
Hi.
I have a students' table with Mark. What I want. I need to check the top 2 people based on the particular subject.
Can you do some videos for this?
forward me if can help you
its a nice tips and by the way what is the name of that song, Chandoo?
It is "If you have got time by Merrie". www.epidemicsound.com/track/madn3HgYvn/
@@chandoo_ Thank you! ☺
Chandoo hi,
If I don’t use office 365 and searching a value from different tables, how can I find this correct value. (Xlookup example)
You can use =IFERROR(VLOOKUP(table1), VLOOKUP(table2)) approach.
One question which is not related to IF function. I have a big database comprising several columns and rows. From this I can find the highest value using MAX function. But how can I find out the cell address of that cell containing the highest value. Please help.
You can then use XMATCH or MATCH to find the row number of the highest value. If you use ADDRESS function, you can generate the address of the cell.
Thank you! But why on earth did Batman get B…?
He wasn't studying in the nights.
In Which version Choose is available ???
It has been around for a long time. The help article says since 2007, but It might be there in 2003 too.
Sir just a small request,please attach a finished and unfinished file so that we can follow along and do it as well as pause the video and try again
Sorry Vishnu. I won't be doing that. I already provide full example files and you can easily remove the formulas if you want to follow along.
@@chandoo_ Yes sir been doing that all along…thank you for the videos 👍.What I meant to ask was I try solving your file before looking up the video or a snippet of the video…So that I can gauge my knowledge so I don’t view the code written..but as you said it’s easy to just delete and start.😊
The one I keep finding myself needing is a way to solve the following:
if(_BIG_LONG_FORMULA_,_BIG_LONG_FORMULA_,_SOMETHING_ELSE_)
When the big long formula is the same in both places, what I really want is not to have the big long formula typed out twice because it makes the whole thing almost unreadable (I'm talking formulas with multiple nested Ifs and lookups). I feel like there has to be a better way, I just can't seem to find it.
You can use LET().
For example =LET(x, BIG_LONG_FORMULA, IF(x, x, something_else))
Also, there are some clever ways to avoid IF. I suggest reading www.excelhero.com/blog/2010/01/i-heart-if.html
@@chandoo_ I think "LET" may be exactly what I'm looking for, thanks!
I'll give you an example of the type of thing I'm trying to model, I have a spreadsheet for keeping track of payroll, but the union contract is insanely complex for the rules to figure it all out, for example, there are shift premiums for working between 1500 and 2300, a higher premium for between 2300 and 0700, and another premium that stacks on top of those if it's between 1500 on a friday and 0700 on a Monday. Additionally all those premiums only apply if more than 2 consecutive hours are worked in the appropriate timeframe, but if more than half of a shift is in a specific timeframe, all of the shift is counted at that rate, unless a higher rate is hit. But if the employee is in overtime all premiums kick in immediately instead of only if more than 2 hours are worked. And let's not even get started on stat-holidays, and super-stat-holidays!
You can imagine that the formula ends up being super complex to capture all of that!
I would probably break the formula to multiple cells and use assumption / input cells so that the final rate calc is a shorter formula.
@@chandoo_ That's what I do, but even so, there's such a dependency on each other cell that it can be hard. Basically I calculate total hours, overtime hours, evening hours, night hours, and weekend hours, then total dollar value.
sir, what will happen if sunday is holiday for all driver?
You can change the formula or mapping table in that case :)