10 Advanced IF formulas every analyst should know

[@Maheshwaripn]

Following you on website for years and today I found you here, immediately subscribed Your examples are like patented inventions for free

[@chandoo_]

Thanks Prashant for the love and support for all these years.

[@erickowaga5473]

I've struggled with the IF function for too long before I came across your channel. You're awesome. Thanks for the great stuff.

[@AnuragSingh-fp6ei]

Chandoo, I have been following you since more than a decade now. Thanks once again for awesome tips and tricks!! These are so useful

[@chandoo_]

Glad you like them!

[@roderickmose4691]

=IF("excellent learning needed"="yes" , "Chandoo" , "")

[@chandoo_]

😊

[@sabitichristopher]

😂

[@rajanghadi5635]

Excellent bro

[@joshuamanampiu6489]

Great stuff

[@vishalbhati912]

Nice formula 😃😃

[@srinivasraogampala4420]

I am using nested if function for lot of years. Now I came to know that there is a IFS function, much readable code.. You are awesome bro.

[@chandoo_]

Great to hear!

[@MrRanjithm1]

You are really superb Chandoo. Coming from the Indian dub continent at 74 years, I started following your tips, and I have understood these formulas much more than before, and I can use them in many of my worksheets I do for a school. Thank you very much.

[@30ktothemoon76]

How come is this free stuff on youtube? This is gem! Thanks for the awesome tips and tricks! Instantly subscribed.

[@mikelib62]

you have a knack of making a difficult, look so simple and matter of fact- you are indeed one of the best on this subject

[@chandoo_]

Thanks Michael.

[@derrickpeoples1864]

Chandoo, you saved my life! I had to use your second example (i.e., Any two out of three conditions) in a real world work assignment, and I got the solution I was looking for, :)

[@akilj9502]

Hello Chandoo, your content is amazing. I love the way you take time and explain every part of the Function / Formula. Thanks a million. You are the best!!

[@m.raedallulu4166]

I've been following you since nearly three years, and you are a wonderful Excel person, Chandoo! Thank you so much!

[@rajasekarans898]

Thanks

[@chandoo_]

Thanks for the love and super 😍

[@shivsharma9153]

So thankful to youtube and creators like you, who making this great knowledge available for free which we would not able to access if there was no youtube.

[@KannadatiBindyaChannel]

Hi Guru, Thanks for your IF and only IF TIPS....!!!! using the IF function for almost a decade from you... the Advanced TIPS to use excel extensively and wisely. keep Going ...!!!

[@NodictatorsinKZ]

You're the best in explaining Excel! Love your channel and your videos, please keep us the good work. Love it.

[@dilansenn]

Thank you very much Sir, I was struggling to use the IF function with wildcards on a major project I was stucked for a long time, this video was a life saver to over come that. Thanks loads and please keep doing this brilliant work. Wish you all the best. God bless you.

[@addhyasumitra90]

Got addicted to see your playlist in last couple of days!! thank you sir :)

[@vramarathnam]

Excellent Demonstration. Great Efforts. Learnt a lot today. Thank You very much

[@Me-ow5rw]

I already learnt from you through your online course. You are Awesome.

[@chandoo_]

Glad to hear that. Thank you so much for going thru the course :)

[@iQuMaster]

There is IFS function?!! Man you just made my life so much easier 😄

[@chandoo_]

You bet!

[@BlockFHatter]

Once again thank you so much. You are very clear and easy to understand the instructions. You make the complicated far less so.

[@chandoo_]

Aww.. thank you for that.

[@sandipgumtya130]

Hi Chandoo, thanks for the amazing tips. Many of these were unknown. Just found that the "on-call" thing can be done with the below formula too. =IF(OR(C8=$C$32:$C$36),"On Call","Not On Call"). Thanks

[@chandoo_]

Great tip!

[@timemerick733]

Really enjoyed the graph example. I added an additional column for average to get a column with a static value and included it in my chart as a line type. It added a pop to the 2nd average overlayed bar showing a dynamic average line. Thanks!

[@chandoo_]

Good trick.

[@rajavintemakhan]

Thank you chandoo not only for this trick also for the excel file you made before to use as database I made some change and implemented in my company as an instant pallet generator still they are using the file everyday without showing any error.. 😊😊

[@sukoi2113]

Awesome gyan. This is the best "If" class.

[@chandoo_]

Thanks a ton.

[@rosedesoloc8081]

Thank you so much for teaching us for free.

[@Oveea-i9e]

Absolutely awesome! So glad I came about your page! :)

[@rory1653]

great examples Chandoo. love the level you're demonstrating; definitely matched the 'advanced' title. cheers

[@chandoo_]

Thanks Rory. :)

[@ishyamkumar3372]

Chandoo Sir Thank you very much for your valuable training videos, Its really useful in my carrier ,

[@patrickschardt7724]

Wonderful examples. I knew some functions existed but never thought to use them with IF

[@chandoo_]

I am glad you found it useful. One powerful way I maximise my learning & memory is by thinking "I know A, I know B, I wonder if I can combine A&B"...

[@patrickschardt7724]

@@chandoo_ that’s a great strategy for learning

[@Skyggespil]

in the wildcard assignment (column O) you dont need to do a countifs. Just write "=if(C8="*"&$O$5&"*","bo person","not a bo person". Great video, I definitely picked up a good handful of pointers

[@chandoo_]

Thank you. hmm.. which version of Excel you are using? When I test it on Excel 365, it doesn't work.

[@cursodeexcelcompletogratis7773]

who can ever dislike this video ? you rocks ! congratulation from Brazil

[@chandoo_]

Aww.. thank you.

[@bintang13t]

Hi Brazilian person, is gratis means free?

[@cursodeexcelcompletogratis7773]

@@bintang13t yes . I am setting a moodle environment for that

[@cursodeexcelcompletogratis7773]

@@chandoo_ welcome. This week I use it for find a zip code in a lista of ranges of zip codes

[@raghvendrasingh9721]

Thank you sir for sharing such information in small videos with brief description and its functionality. It really helps

[@chandoo_]

You are most welcome

[@HacTacbka]

your videos are straight to the point and it pleases me so much! I'm hooked from the first second =) I tried looking similar videos by other exel-pros, but yours are interesting, quick and with great examples. Thank you!

[@jitendrapatel1831]

This guy is genius in excel. All the best Chandu.

[@chandoo_]

Thank you Jitendra...

[@taizoondean689]

Was revising Thank You again and God Bless You

[@aivie1900]

Thank you so much for this Sir, I found the great examples I needed for my work. More power to your channel. God Bless.

[@michaelvincentmamongcara1208]

Hi Chandoo, loving your videos. For inventory analysis in manufacturing/warehousing, hope you can give us an excel method on how to determine the FEFO or "First Expiry/First Out" - *Fresher production dates are served instead of older ones* affected items. Considering the age requirements of multiple customers. This might be a bit complex, but hope you can give a simple and easy way.

[@Patrick-ge2zn]

Thankyou for the video, the cell formatting is much easier than the way I was doing it .

[@chandoo_]

Glad it helped

[@amihem1080]

👍 Following you last many year...you are awasome

[@chandoo_]

Thank you Amit 😀

[@wayneedmondson1065]

Hi Chandoo. Awesome IF function workout! Thanks for sharing :)) Thumbs up!!

[@vitorpontes1048]

Loved your tips and tricks. You're awesome. Thank you for spending the time to teach is us such awesome features. Keep going!

[@chandoo_]

My pleasure!

[@prashantsteele]

Life changer Lessons... No words more needed... Thanks Chandoo

[@chandoo_]

My pleasure

[@JustOkko]

Wow! I thought I had advanced IF skills, but this adds some to my portfolio. Thanks!

[@chandoo_]

Glad it was helpful!

[@ayazmewati3194]

Sir your simplicity is exemplary

[@gavidiharish13]

What a trainer.... ❤❤❤

[@silasalexbob]

Hi Chandoo! Awesome video! The boolean was so cool. I have a doubt @12:35 - I used "=IF(COUNTIF($C$32:$C$36,C8),"On call","Not on call")" instead of your Countifs formula and it works the same for me. Any reason you are preferring the Countifs?

[@chandoo_]

Both COUNTIF and COUNTIFS work in this situation. I always use xIFS version because they give me an option to add conditions in future. One less thing to remember too.

[@blazerbeavs2333]

I didn't know you could embed dates in your formulas! I use the between formula frequently and always have date inputs in cells. Cool stuff!

[@vaibhavmhaske173]

Thank you...chandoo sir Your videoa are ao precise and to the point.. I am finding it very easy to learn and work

[@chandoo_]

It's my pleasure

[@jet-joyexceltraining8418]

Every learn something new from your videos

[@tapanbarick9572]

Sir I like your teaching style.

[@PriyankaLiyanage]

Thank you very much Mr. Chandoo. ♥

[@satishgundoji1144]

one of the best channels!

[@chandoo_]

Thank you :)

[@qasmb1546]

Hi, Excellent video! Can I ask why you put an extra opening bracket for the Any two out of three conditions formula? what does that do exactly? Thank you.

[@chandoo_]

Hmm.. may be you are not using Excel 365..?

[@qasmb1546]

@@chandoo_ I am using excel 2016.....Does that formula only work in excel365?

[@arnabsaha33]

Interested in your excel school course. Do I get live class or only recorded video? Do I get to download the videos?

[@peerzsalim1722]

Hi Mr.Chandoo, Thanks for your effort. Best Wishes

[@veronikamihaylova9704]

Hi Chandoo, for the first example, why I can't use OR instead of XOR please?

[@RA-rh5lb]

Thanks, Chandoo for ifs example here.. definitely i didn't understand boolean methos.. confusing

[@Zaimulwaie]

i'm confused regarding the boolean replacement part. if the department is sales and 2018, wouldnt 2% - 1(because its true)*1% make it 1%. what does the '-' sign in front of 2% mean? is it a subtraction or does it perform a different function, because i notice there are spaces before and after the '-' sign

[@mickaell3451]

The boolaen replacement trick and a median used for dates trick are a good way to confuse the colleagues. Im definitly gonna use it :D Thanks Chandoo

[@chandoo_]

I find that both of them also offer some speed improvements too.

[@mickaell3451]

@@chandoo_ i didnt think of this aspect. thanks for this detail.

[@keishrich]

These were helpful, thank you Chandoo.

[@chandoo_]

You are welcome Keisha.

[@HardikPatel-fn2jb]

can we use =if(sumif()

[@ruthadere7314]

very important, thank you!

[@chandoo_]

Glad it was helpful!

[@heather7461]

You're amazing, thank you so much !

[@chandoo_]

You are welcome Heather.

[@francisaranha]

Just loved it Chandoo!

[@inher30s41]

Can anyone please explain, why did he considered =2%-, instead of =2%+? (video time 10:20)

[@osirkeci]

very good lesson, thank you, Chandoo

[@chandoo_]

You are welcome Omer...

[@krishnakishortiwari8724]

how I can use the lookup function where you used countifs function?

[@chandoo_]

Use it like this =IF(XMATCH(your_value, on_call_list, 0),"On call", "Not on call")

[@pavancena98]

I have a large dataset with more than 5lac line items.. how can I work in excel? Does power query or power pivot or anyother tool helps?

[@defrinaelsianipar9883]

thats what i need, thank you my brother 👌😁

[@chandoo_]

I am glad you enjoyed it...

[@flickafy]

Great tips Chandoo, thank you. Glad you've launched a KZbin channel too in addition to your excellent website - the comments section of those has always been a useful read too. I learnt a lot from this video, not just from the bonus and Boolean tips, but some nice chart ones too, thanks! Was wondering about the XOR "exclusive or", given it has excluded where an individual meets both criteria. Is there an alternative formula you'd recommend so that someone who meets both doesn't get excluded since they qualify for both? Thanks!

[@chandoo_]

Thanks. Yes, I always feel like comments on the site & now here on YT is a gold mine for learning new things. You can use the good old OR function in your case btw.

[@dharagajera2902]

Excellent!!!!! Can you mentioned how many videos have to watch which is necessary for data analysis beginner as can not able to watch each and every video?? Please give reply Thanks for everything. Say yes to chandoo.....

[@ragavendra77]

Amazing Chandoo 👏

[@ozgur937]

This is such a beautiful representation.

[@chandoo_]

Thank you 😊

[@bobwhelan5644]

Brilliant Chandoo - such very useful tips, thank you

[@chandoo_]

My pleasure!

[@florincopaci6821]

Great work! Like always i m impressed.Thank you for sheering this

[@chandoo_]

Thank you florin...

[@canirmalchoudhary8173]

CHANDOO YOU ARE REALLY GENIUS

[@chandoo_]

Thank you 😍

[@christianreed4396]

Wonderful! Thank you for this!

[@chandoo_]

Glad you enjoyed it!

[@sophiapradal1054]

Hello how would I write the IF formula if column AE is a certain word “levatrice” then change font colour of columns AF AG AH AI AJ AK AL AM AN to all be orange font colour. ???

[@siddiqbinsalam1284]

Excellent work brother, thanks.

[@chandoo_]

My pleasure

[@falaksharma1]

Thanks Sir for such a great video.

[@leostar1100]

Need help: how to replace date to null in a column so i can retain value in column other then date so that i can fill null with retained value

[@nirmalabolar1382]

Awesome video, very informative, thanks

[@chandoo_]

Glad it was helpful!

[@meniporat3527]

A shorter solution to the "Review" challenge: =IF(AND(YEAR(F8)=2019,MONTH(F8)

[@raitup00]

I didn't expect the Pop quiz!! Saludos desde México

[@chandoo_]

I hope you passed it ;)

[@mohamedadjal8502]

hi, Professor , I have a question: If we have numbers in different formats, for example: kg, liters, meters ... When we do research (we use search functions) in the cells that contain these numbers, can we get numbers with their different formats? Thank you .

[@chandoo_]

You can do this in one of the two ways: 1. You can use either IF or lookup to add format value at the end of search result. 2. Use number format codes to add the unit of measurement at the end. - For this method, refer to tip #4 in this video - kzbin.info/www/bejne/aKHRZWqXl792jbc

[@lesleywells3497]

Excellent content!

[@AnuragKumar-zk8ug]

I have only one question based on the basic excel or advanced excel what kind of job I can get? Please reply to me I'm very keen to know.

[@anuradhatomer5801]

Thankyou so much for this tricks❤️

[@giandenorte]

Another great content Master Chandoo! Greetings from Philippines.

[@chandoo_]

Hello there!

[@AdityaKumar-br1dx]

thanks sir really helpful i got confused when there are so many if constraints in the formula.

[@briandennehy6380]

Great stuff, love the countif trick 👍

[@chandoo_]

Thank you! Cheers!

[@aseemgoyal101]

One word....Awesome...keep going mate

[@chandoo_]

😀

[@GeorgeAJululian]

Thank you it was very helpful

[@chandoo_]

You are welcome

[@Rock32188]

Hi Mr. Chandoo Just wanted to know how to use the formula "if" in excel for these kinds of bonus in amount, for example, there is a given target in 5 conditions for the getting bonuses in amount. 600$=30$ 700=40$ 800=50$ 900=60$ 1000=80$. now one employee has a target of 600$ he achieved will eligible for 30$ and the second employee has a target of 700$ but achieved 600$ it means if I enter 600$ this will make eligible, but basically, she did not reach the target to be eligible for it. Please help me out

[@algr665]

Man, you are really good. Love your excel tips. Great job. I just subscribed.

[@chandoo_]

Thanks for the sub!

[@shirsN]

Is it possible to just highlight one column in conditional formatting based on other column? In your exmaple you are highlighting all rows. E.g if name is abc and another column having status and values as present, absent and na. So now i want to highlight absent value from column status where abc is the person; instead of the entire row.

[@chandoo_]

You can. Just select the column you want to highlight, insert a new formula based rule and write the rule. Give it a try and let me know if you have questions.

[@shirsN]

@@chandoo_ for me even the tip mentioned in this video about condition format is not working. I will make this work 1st and get back to you. Wondering why it is not working even if i followed the steps you are doing

[@shirsN]

@chandoo my issue why it was not working was because of wrong referencing (absolut/relative) i was able to achieve this by doing and (a1=$e$1, b1=$f$1). So suppose a column has name n b column has status and i wanted to high light present with green then i need to use above formula. In $e$1 i have name as abc to search and in $f$1 i will put value as present . In this case it will high light where name is abc and status is present with green color. This is exactly what i wanted to do. I will see if i can do this directly on column with out specifying it in e n f column with absolute/relative/ mix referencing.

[@balaroxx2700]

why the answer for median says 69120 for procurement whem we mentioned as website

[@balamira297]

Can we do the first example using "or" function instead?

[@chandoo_]

You can.

[@gulshansharma2279]

Dear sir I have a doubt in excel Suppose We have a data between 1-Aug-2020 to 30-oct-2020 , how we can calculate the sum of 22.Aug to 10.Sept ? Please help....