5 Advanced Tips on how to use IF formula in Excel that will SURPRISE you 😲

Поделиться
HTML-код
  • Опубликовано: 5 сен 2024

Комментарии • 166

  • @Seftehandle
    @Seftehandle 2 года назад +16

    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

    • @chandoo_
      @chandoo_  2 года назад +1

      You are welcome Tine 😀

  • @salehmuhammad6811
    @salehmuhammad6811 2 года назад +11

    👍 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.

    • @chandoo_
      @chandoo_  2 года назад

      My pleasure Saleh.

  • @PrashantKg1996
    @PrashantKg1996 2 года назад +5

    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

  • @MAH8897
    @MAH8897 2 года назад +4

    Hi Chandoo,
    I enhance my knowledge.
    "THANK YOU"
    Regards

    • @chandoo_
      @chandoo_  2 года назад +1

      You are welcome Mohammed. Please share what you are learning with others and help colleagues in need.

    • @MAH8897
      @MAH8897 2 года назад

      @@chandoo_ ofcourse I do.

  • @shiffamohammed5818
    @shiffamohammed5818 2 года назад +2

    Thanks a lot Chandoo for sharing such an amazing video. Another versatile use of if function!

  • @wrognOne
    @wrognOne 7 месяцев назад

    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

  • @arunkumar-ue1il
    @arunkumar-ue1il 2 года назад +2

    As always you are awesome & make concepts simplified, Thank you!

    • @chandoo_
      @chandoo_  2 года назад +1

      Glad you like them!

  • @nasirmuhammad3901
    @nasirmuhammad3901 Год назад

    Your method of teaching is simpler. Well Done!

  • @peter-e2q
    @peter-e2q Год назад

    Never realised about the black end bracket! Thank you!!

  • @ronebhowmick
    @ronebhowmick Год назад

    Thanks for your teaching method

  • @JackKing12.
    @JackKing12. Год назад

    Excellent....good to see your pivot table advice.

  • @surajbhardwaj428
    @surajbhardwaj428 2 года назад +1

    IFS & two Xlookup function liked most... 👌, Great work sir,,... Both function will help me sort out daily work in office....🙏🙏

    • @chandoo_
      @chandoo_  2 года назад

      Awesome to hear that Suraj.

  • @yemiakinwande7039
    @yemiakinwande7039 2 года назад

    very helpful video - ifs and choose is new and useful for me. many thanks

  • @Jay-Atom50
    @Jay-Atom50 Год назад

    Awesome information and tips. Thank you

  • @shriidharkulkarni6648
    @shriidharkulkarni6648 Год назад

    You are really awesome teacher of Excel.

  • @lealeveriza4497
    @lealeveriza4497 Год назад

    👏👏👏 I'm so happy I found your channel! You're a good teacher.👍

  • @manissery1956
    @manissery1956 2 года назад

    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.

    • @chandoo_
      @chandoo_  2 года назад

      I have a video on LAMBDA here. studio.ruclips.net/user/videoYrMCkE59ef4/edit Check it out

  • @Golfina19
    @Golfina19 Год назад

    Great video. Thanks for all the tips.

  • @selormselorm8535
    @selormselorm8535 Год назад

    I love you clips. Thank you sir!

  • @vullikantisaidheeraj7634
    @vullikantisaidheeraj7634 2 года назад +1

    Thanks chandoo sir
    Instead of writing a long if function for grading we can also use vlookup by keeping it as a approximately match.

    • @chandoo_
      @chandoo_  2 года назад

      We can. As the topic is about IF, I stayed with IF based examples.

  • @vijayasakthivelvigneshwari7513

    Thanks chandoo you made my day

  • @saaneroa5024
    @saaneroa5024 5 месяцев назад

    Awesome video Chandoo

  • @aiasaiascon3894
    @aiasaiascon3894 2 года назад

    Very nice to have this CHOOSE!!! Thank you Chandoo!!!!

    • @chandoo_
      @chandoo_  2 года назад

      Glad you like it!

  • @mkajiwala1
    @mkajiwala1 7 месяцев назад

    Superb sir...... Very good examples, and brief too, thanks

    • @chandoo_
      @chandoo_  7 месяцев назад

      You are most welcome

  • @dvictorjayaseelan8889
    @dvictorjayaseelan8889 2 года назад

    You make learning so easy and impressive...

  • @mahmoudmohsen8105
    @mahmoudmohsen8105 2 года назад

    cool that index/choose tip and the xlookup thanks alot

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome Mahmoud.

  • @arbazahmad7177
    @arbazahmad7177 2 года назад

    Thanks 😊 for this great using tip of "if not found" option in Xlookup function.

    • @chandoo_
      @chandoo_  2 года назад

      Glad it was helpful!

  • @kazimuhammadyakubali340
    @kazimuhammadyakubali340 Год назад

    tnx a lot

  • @frenan82
    @frenan82 2 года назад +1

    Thanks!

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome :)

  • @arvindr2101
    @arvindr2101 2 года назад +1

    Good afternoon sir, Thankful for the important video

    • @chandoo_
      @chandoo_  2 года назад

      Good day Aravind and you are welcome :)

  • @chrism9037
    @chrism9037 2 года назад +1

    Chandoo, another outstanding video, thanks!

    • @chandoo_
      @chandoo_  2 года назад

      Glad you enjoyed it Chris.

  • @AmanaButt
    @AmanaButt 2 года назад

    very well explained with good examples. Worth watching. Thanks

  • @legacygk2811
    @legacygk2811 2 года назад

    Amazing stuff! Thank you so much.

  • @taizoondean689
    @taizoondean689 2 года назад +1

    Thank you sir 🙏

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome TD.

  • @srmchinni3
    @srmchinni3 2 года назад

    Thank you, Chandu. That's brilliant.

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome Sairam.

  • @AP-eb8hd
    @AP-eb8hd 2 года назад

    IFS was a take away !! thanks..

  • @leehueyyun630
    @leehueyyun630 2 года назад

    You are awesome.

  • @karansuroshe6236
    @karansuroshe6236 2 года назад

    Thanks sir 🙏

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome Karan.

  • @dennisvilcatomalopez2245
    @dennisvilcatomalopez2245 Год назад

    Gracias por compartir los tips. Eres un grande 💯

  • @tanmaypalkar9861
    @tanmaypalkar9861 Год назад

    Thank you Chandoo :)

  • @nithin51988
    @nithin51988 2 года назад

    You are really awesome chandu...,short and crispy videos makes easy to understand...

    • @chandoo_
      @chandoo_  2 года назад

      Thank you so much 🙂

  • @vijayarjunwadkar
    @vijayarjunwadkar 2 года назад +1

    Great video Chandoo! I learnt a few new tricks to use with IF and thanks a lot for that! 😊👍

    • @chandoo_
      @chandoo_  2 года назад

      Great to hear that Vijay.

  • @SKMRify
    @SKMRify 2 года назад

    Good video.

    • @chandoo_
      @chandoo_  2 года назад

      Glad you enjoyed it

  • @radheshyamthakur
    @radheshyamthakur 2 года назад

    Good concept especially the last one.

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome Radhe.

  • @raziajeena9861
    @raziajeena9861 2 года назад

    V well explained thanks

  • @Rattuu
    @Rattuu Год назад

    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.😊😊😊

  • @yuthakrainarong265
    @yuthakrainarong265 Год назад

    Sir, you had my bow.

  • @RMKserve
    @RMKserve 2 года назад

    Haaa super tips❤️😘😘❤️👍

  • @maheshthorat7568
    @maheshthorat7568 2 года назад

    You are a owesome human being.🥳

  • @Saikumar-yp1lx
    @Saikumar-yp1lx 2 года назад

    Your videos are makes me excel pro

  • @boboshanu7325
    @boboshanu7325 2 года назад

    Bell notification on only for you brother ✌️

  • @alwayspositive999
    @alwayspositive999 Год назад

    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?

  • @MM-qk4if
    @MM-qk4if Год назад

    Excellent👍

  • @technicalwords8214
    @technicalwords8214 2 года назад

    If formula se nikli gayi value ka sum kr sakte ya nhi please guide sir

  • @anuradhabadade7239
    @anuradhabadade7239 2 года назад

    Thank you Sir...

  • @alializadeh8195
    @alializadeh8195 2 года назад

    Thanx

  • @basicinfoforall7306
    @basicinfoforall7306 2 года назад

    I'm trying to learn data science from your content.

  • @vickyat4675
    @vickyat4675 2 года назад

    Salaam chandoo Bhai,,,

  • @Munichandra_Reddy
    @Munichandra_Reddy 2 года назад

    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

  • @hobsondrake
    @hobsondrake 2 года назад

    Chandoo, the excel Super Hero.

    • @chandoo_
      @chandoo_  2 года назад

      🦸‍♂️ We all are...

  • @digitalinfoMRU
    @digitalinfoMRU 2 года назад

    Thanks 🙂
    It helps a lot..

    • @chandoo_
      @chandoo_  2 года назад +1

      You are welcome DI.

  • @TheHanuwant
    @TheHanuwant Год назад

    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?

  • @SumiaA
    @SumiaA Год назад

    I don't have the xlookup function is there an alternative function to use?

  • @pankajpandey4485
    @pankajpandey4485 2 года назад

    Basic to intermediate PowerPoint lesson or series.
    Please Sir

  • @canirmalchoudhary8173
    @canirmalchoudhary8173 2 года назад

    If not found can be good alternative for second version of INDEX.

  • @ericgroen253
    @ericgroen253 Год назад

    interesting! But in your examples you typed , in stead of ; That might be a version error? I only got it to work with the ; 🙂

    • @chandoo_
      @chandoo_  Год назад

      Thank you. In European version of Excel ; is used and almost everywhere else , is used

  • @syedsyed777
    @syedsyed777 2 года назад +1

    Hi sir,
    Can I know which video editing software u r using ?
    Will be a great help!

    • @chandoo_
      @chandoo_  2 года назад

      I use Camtasia for creating and editing my videos. You can get a free trial from here - techsmith.pxf.io/c/3169105/481530/5161

    • @syedsyed777
      @syedsyed777 2 года назад

      @@chandoo_ I would like to thank you for taking out your time and replying me.
      Thank you so much...

  • @ankitBhartiya2
    @ankitBhartiya2 2 года назад

    As always you are nice. Please make video on Microsoft Office Specialist: Associate" Certifications MO-200, MO-201. Are they really worth ?

    • @chandoo_
      @chandoo_  2 года назад +1

      Thanks Ankit. I will share my views on certifications in an upcoming video.

  • @ShivaSingh-vs4yv
    @ShivaSingh-vs4yv 2 года назад +1

    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. 😍

    • @chandoo_
      @chandoo_  2 года назад

      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 😎

  • @ArifKhan-qo9jw
    @ArifKhan-qo9jw 2 года назад

    Xlookup has covered mostly formulas so everyone should learn xlookup 😊

  • @bhoopendraraghav9781
    @bhoopendraraghav9781 2 года назад

    Very Nice

  • @supriyaagrawal432
    @supriyaagrawal432 2 года назад

    Great I saw many videos but everyone inform basic tips only

    • @chandoo_
      @chandoo_  2 года назад

      You are welcome Supriya.

  • @Explorer_Nur
    @Explorer_Nur 2 года назад

    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

  • @Metzanine
    @Metzanine 2 года назад +2

    Nested Xlookups? 🤯🥳

  • @ruchikachauhan9697
    @ruchikachauhan9697 2 года назад

    Your content is very helpful . Would love to know if you have a patreon account?

  • @7fink7
    @7fink7 Год назад

    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! 🙃👍

  • @MrDushyant123
    @MrDushyant123 Год назад

    How to use for value and max values xlookup

  • @shriram8088
    @shriram8088 2 года назад

    Hey chandoo
    Thanks for the video.
    Chandoo, i want to learn VBA codeing. Do you have paid course for that.

    • @chandoo_
      @chandoo_  2 года назад

      Yes I do. Please see chandoo.org/wp/vba-classes/

  • @kunalsyal3658
    @kunalsyal3658 19 дней назад

    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

  • @bibekkoirala5294
    @bibekkoirala5294 2 года назад

    its a nice tips and by the way what is the name of that song, Chandoo?

    • @chandoo_
      @chandoo_  2 года назад

      It is "If you have got time by Merrie". www.epidemicsound.com/track/madn3HgYvn/

    • @bibekkoirala5294
      @bibekkoirala5294 2 года назад

      @@chandoo_ Thank you! ☺

  • @LearnAllways
    @LearnAllways 2 года назад

    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?

  • @rashadagayev3108
    @rashadagayev3108 2 года назад

    Switch function also can you

  • @sahithikoluguri9539
    @sahithikoluguri9539 5 месяцев назад

    I need this video in Brother

  • @iqbalsingh6311
    @iqbalsingh6311 Год назад

    Instead of Choose, text function with Xlookup is better alternative

  • @teoxengineer
    @teoxengineer 2 года назад

    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)

    • @chandoo_
      @chandoo_  2 года назад

      You can use =IFERROR(VLOOKUP(table1), VLOOKUP(table2)) approach.

  • @manissery1956
    @manissery1956 2 года назад

    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_
      @chandoo_  2 года назад +1

      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.

  • @iqbalsingh6311
    @iqbalsingh6311 Год назад

    X lookup or Vlookup with approximate match is Better than nested if

  • @probashibangla6708
    @probashibangla6708 11 месяцев назад

    ❤❤❤

  • @shaikjeelani6179
    @shaikjeelani6179 2 года назад

    Do "and" ,"or" multiple conditions in single formula of if

    • @chandoo_
      @chandoo_  2 года назад

      Please see the video I mention at the end. It covers those topics - ruclips.net/video/-yFpzIRifK4/видео.html

  • @statesticsandanalyses6348
    @statesticsandanalyses6348 2 года назад

    In Which version Choose is available ???

    • @chandoo_
      @chandoo_  2 года назад +1

      It has been around for a long time. The help article says since 2007, but It might be there in 2003 too.

  • @JEMS-DILAN
    @JEMS-DILAN 2 года назад

    sir, what will happen if sunday is holiday for all driver?

    • @chandoo_
      @chandoo_  2 года назад

      You can change the formula or mapping table in that case :)

  • @vishnu8899
    @vishnu8899 2 года назад

    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

    • @chandoo_
      @chandoo_  2 года назад +1

      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.

    • @vishnu8899
      @vishnu8899 2 года назад

      @@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.😊

  • @Green__one
    @Green__one 2 года назад

    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.

    • @chandoo_
      @chandoo_  2 года назад

      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

    • @Green__one
      @Green__one 2 года назад

      @@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_
      @chandoo_  2 года назад

      I would probably break the formula to multiple cells and use assumption / input cells so that the final rate calc is a shorter formula.

    • @Green__one
      @Green__one 2 года назад

      @@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.

  • @mehboob876
    @mehboob876 2 года назад

    I have an excel list of approximately 1 million part numbers. I need an excel pro to change part numbers of any duplicates on the list. For example if there are duplicate part numbers:
    1234
    1234
    1234
    I need this changed by adding "A" "B" etc for example :
    1234 - A
    1234 - B
    1234 - C
    etc
    1234 - AA
    1234 - AB
    etc.
    The new part number should be listed on the next column. How to do...

    • @oliverscheurich9202
      @oliverscheurich9202 Год назад

      =CONCATENATE(A1,"-",VLOOKUP(COUNTIF($A$1:A1,A1),$F$1:$G$600,2,FALSE))
      Enter this formula in cell B1 and copy down. The part numbers are in column A.
      =SUBSTITUTE(ADDRESS(1,F1,4),"1","")
      Enter this formula in cell G1 and copy down to G600. In range F1:F600 are numbers 1, 2, 3, 4 and so on.

  • @Abhi2533
    @Abhi2533 2 года назад

    🔥🔥🔥

  • @biopunch1136
    @biopunch1136 2 года назад

    Thank you! But why on earth did Batman get B…?

    • @chandoo_
      @chandoo_  2 года назад

      He wasn't studying in the nights.

  • @vargheseantony5677
    @vargheseantony5677 2 года назад

    👍

  • @bondnikunj
    @bondnikunj 2 года назад +1

    Awesome but I ran out of new things to learn in excel. Next sql? Or power bi or anything for that matter just something new to learn. Excel hardly anything is new after watching so many of your videos

    • @chandoo_
      @chandoo_  2 года назад

      Sure, there are plenty of videos on other topics on the channel and there are plenty of other channels too. Pick what you want to learn and go for it :)

    • @bondnikunj
      @bondnikunj 2 года назад

      @@chandoo_ I would love it if you could bring sql contents really. Is it possible?

    • @bondnikunj
      @bondnikunj 2 года назад

      @@chandoo_ I only come to your channel to brush up excel. Loyality 😂 love and enjoys your content. Although the editing of memes and funny sounds in btw topics do have scope of improvement 😋

  • @melvynplay6975
    @melvynplay6975 2 года назад

    Sound at 7:08 😂

  • @technophile01
    @technophile01 2 года назад

    hello. I made a table on excel Android with 30 boxes corresponding to the days of the month. each day I enter my expenses. I would like excel to automatically calculate for me in a box the average of the previous entries thank you

    • @chandoo_
      @chandoo_  2 года назад

      You can use AVERAGEIFS() and exclude future dates.

    • @technophile01
      @technophile01 2 года назад

      @@chandoo_ Hello. I am not a specialist and I do not know how to do it concretely, you would be very kind to write me the formula if you wanted. thank you so much. I remind you that I would like the average to be calculated each day, for example on the 4th day that of 4 values ​​the 5th that of 5 values ​​etc.. until the 30th day.