10 Advanced IF formulas every analyst should know

Almost 70% of spreadsheets contain IF formula. So how well do you know it? In this ADVANCED video, learn all about:
-- Using IF to check ONLY One of condition
-- Any Two out of Three conditions
-- Between condition with MEDIAN function
-- Replacing nested if formulas with NEW IFS function
-- Speeding up IF logic with Boolean replacement hack
-- Check List membership
-- Using Arrays with if (MedianIF example)
-- Wildcard comparisons in IF formula
-- Using IF with Conditional Formatting
-- IF + Charts for awesome results
Timestamps:
===========
0:00 - Intro
0:25 - Only One of condition (XOR)
2:12 - Any two out of three conditions
4:58 - Between formula (AND method & MEDIAN method)
7:00 - Nested IF replacement with IFS()
9:03 - Pop Quiz
9:16 - Avoiding IF formulas with Boolean Replacement Hack
11:42 - List membership check with IF formula
13:35 - Using Arrays with IF - Median IF example
16:10 - Wildcard check with IF
17:45 - Conditional formatting with IF
19:48 - Charts with IF formula
21:23 - Closing remarks
Example files:
===========
chandoo.org/wp/advanced-if-tr...
👉Checkout my online course on Advanced Excel & Data analysis
===================================================
💻📗💥 chandoo.org/wp/excel-school-p...
#IFLove #Excel

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

[@roderickmose4691]

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

[@chandoo_]

😊

[@sabitichristopher]

😂

[@rajanghadi5635]

Excellent bro

[@joshuamanampiu6489]

Great stuff

[@vishalbhati912]

Nice formula 😃😃

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

[@m.raedallulu4166]

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

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

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

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

[@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!

[@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!

[@NodictatorsinKZ]

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

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

[@Oveeya]

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

[@taizoondean689]

Was revising Thank You again and God Bless You

[@wayneedmondson1065]

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

[@bindyajnana]

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

[@rory1653]

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

[@chandoo_]

Thanks Rory. :)

[@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!!

[@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, :)

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

[@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!

[@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!

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

[@rosedesoloc8081]

Thank you so much for teaching us for free.

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

[@ardip.6930]

Thanks buddy...I think I might use a couple of these going forward 😁

[@lesleywells3497]

Excellent content!

[@francisaranha]

Just loved it Chandoo!

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

[@christianreed4396]

Wonderful! Thank you for this!

[@chandoo_]

Glad you enjoyed it!

[@florincopaci6821]

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

[@chandoo_]

Thank you florin...

[@ishyamkumar3372]

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

[@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 :)

[@raitup00]

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

[@chandoo_]

I hope you passed it ;)

[@acommonman155]

Loved it totally

[@falaksharma1]

Thanks Sir for such a great video.

[@heather7461]

You're amazing, thank you so much !

[@chandoo_]

You are welcome Heather.

[@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!

[@ayazmewati3194]

Sir your simplicity is exemplary

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

[@gavidiharish13]

What a trainer.... ❤❤❤

[@sukoi2113]

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

[@chandoo_]

Thanks a ton.

[@Patrick-ge2zn]

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

[@chandoo_]

Glad it helped

[@tlc1795]

Thank you !

[@peerzsalim1722]

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

[@nirmalabolar1382]

Awesome video, very informative, thanks

[@chandoo_]

Glad it was helpful!

[@silverfunnel6819]

Very helpful, thank you!

[@chandoo_]

Thank you Silver...

[@andreluizsales3286]

Thanks Chandoo!

[@TechWrite]

Great video Chandoo. Many thanks!

[@chandoo_]

My pleasure!

[@ruthadere7314]

very important, thank you!

[@chandoo_]

Glad it was helpful!

[@siddiqbinsalam1284]

Excellent work brother, thanks.

[@chandoo_]

My pleasure

[@satishgundoji1144]

one of the best channels!

[@chandoo_]

Thank you :)

[@prashantsteele]

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

[@chandoo_]

My pleasure

[@briandennehy6380]

Great stuff, love the countif trick 👍

[@chandoo_]

Thank you! Cheers!

[@flickafy]

Great tips Chandoo, thank you. Glad you've launched a RUclips 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.

[@ragavendra77]

Amazing Chandoo 👏

[@algr665]

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

[@chandoo_]

Thanks for the sub!

[@keishrich]

These were helpful, thank you Chandoo.

[@chandoo_]

You are welcome Keisha.

[@FRANKWHITE1996]

As always great video thank you

[@chandoo_]

Very welcome :)

[@bobwhelan5644]

Brilliant Chandoo - such very useful tips, thank you

[@chandoo_]

My pleasure!

[@giandenorte]

Another great content Master Chandoo! Greetings from Philippines.

[@chandoo_]

Hello there!

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

[@shaitarun]

Amazing ❤

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

[@tapanbarick9572]

Sir I like your teaching style.

[@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!

[@jet-joyexceltraining8418]

Every learn something new from your videos

[@iQuMaster]

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

[@chandoo_]

You bet!

[@michafabich9213]

very valuable

[@amihem1080]

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

[@chandoo_]

Thank you Amit 😀

[@nandakishorecb2958]

Superb... thank you so much :-)

[@chandoo_]

😀😍

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

[@alializadeh8195]

Thanks man

[@chandrasekharnayak1589]

Thank u sir

[@johndurran614]

Another awesome video

[@chandoo_]

Glad you enjoyed it

[@ozgur937]

This is such a beautiful representation.

[@chandoo_]

Thank you 😊

[@uwadiegwuemmanuel8809]

I am grateful

[@QuantuMatrix]

Fantastic

[@agape13]

Genius! 🏆

[@khalidrehman5045]

Thanks Chandoo for another great video

[@chandoo_]

My pleasure!

[@RobertAfrick]

I am in the presence of greatness...thanks Chandoo!

[@chandoo_]

Wonderful!

[@aseemgoyal101]

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

[@chandoo_]

😀

[@GeorgeAJululian]

Thank you it was very helpful

[@chandoo_]

You are welcome

[@surajbhardwaj428]

Love bro...

[@osirkeci]

very good lesson, thank you, Chandoo

[@chandoo_]

You are welcome Omer...

[@defrinaelsianipar9883]

thats what i need, thank you my brother 👌😁

[@chandoo_]

I am glad you enjoyed it...

[@enelesmarketing7389]

mantapp bagosss

[@aman_mashetty5185]

Thank you Chandoo for Another great video👌👌

[@chandoo_]

Glad you enjoyed it

[@JustOkko]

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

[@chandoo_]

Glad it was helpful!

[@Minhasacademy]

Thank you so much chandoo sir ❤️

[@chandoo_]

Always welcome

[@jitendrapatel1831]

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

[@chandoo_]

Thank you Jitendra...

[@mr.chinaski2613]

Awesome content, you're the god of Excel :D

[@chandoo_]

Aww. Thank you. I enjoy learning and sharing

[@arnabsaha33]

You are a magician.

[@pektuspektus612]

Thanks Chandoo!
I'm from Philippines too.
Lots of new stuffs form me.

[@chandoo_]

So cool!

[@frenan82]

Amazing!

[@chandoo_]

You are!

[@kvdatascience2905]

u r a star...

[@canirmalchoudhary8173]

CHANDOO YOU ARE REALLY GENIUS

[@chandoo_]

Thank you 😍

[@RohitKumar-cc7zi]

Very good bro just keep it up

[@chandoo_]

Thanks

[@mayurzope6531]

Chandoo you are awesome man..keep going...

[@chandoo_]

Thanks ✌️

[@ggmrb]

Your are the best!!

[@chandoo_]

😀