Excel's IF Function Explained | How to Avoid Performance Issues

[@MyOnlineTrainingHub]

➡Download the example file here: bit.ly/iffunction24file Master Excel with my courses: bit.ly/iffunction24courses

[@IvanCortinas_ES]

Excellent tutorial and practical. The important thing is to have the examples with cases that are close to real life and you do it. Thank you very much Mynda. Learning from a great teacher!

[@MyOnlineTrainingHub]

Great to hear, Ivan!

[@retief5057]

You describe things simple and to the point. Brilliant !!

[@MyOnlineTrainingHub]

So pleased you find my tutorials helpful :-)

[@wayneedmondson1065]

Hi Mynda. The multi-nested IF example was classic. Thanks for showing people how to think of doing a lookup instead of IF, IF, IF :)) Thumbs up!!

[@MyOnlineTrainingHub]

😁 Thanks, Wayne!

[@davidclayton2512]

Another advantage of using lookup is if you wish to introduce a new 'if' such as 120 days = "legal letter",then the lookup range is easier to amend than the nested if formula.

[@MyOnlineTrainingHub]

Yes, indeed!

[@apamwamba]

WOW! Excellent Video!!. And the extra benefit is that this knowledge can be applied in any programming language and SQL. Its called Decision Making...We are very lucky to have our very own MVP : Mynda

[@MyOnlineTrainingHub]

Thanks so much!

[@pascou8322]

You always explain Excel topics in such a way anyone can understand. :)

[@MyOnlineTrainingHub]

Glad to hear that, Pasco!

[@darrylmorgan]

Hi Mynda!Great Run Through And Explanation Of These Awesome Functions...Thank You :)

[@MyOnlineTrainingHub]

Thanks for watching, Darryl!

[@teoxengineer]

Mynda thank you. Can you make a tutorial about the DGET function and its sub formulas.

[@MyOnlineTrainingHub]

Will add it to my list.

[@Giovanni-vx8xl]

very clever way in how to use dates with tables and vlookup instead of if statements. thanks !

[@MyOnlineTrainingHub]

Glad you like it!

[@DirectDilSe29]

Thank youuuuu... I was looking for this formula from long time and can you upload one video for all benefits of XLookUp? Thanks once again

[@MyOnlineTrainingHub]

Here's a comprehensive tutorial on XLOOKUP: kzbin.info/www/bejne/aIfMfqBjrNqAabc

[@DirectDilSe29]

@@MyOnlineTrainingHub thank you

[@chrism9037]

Super helpful, thanks Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@GeertDelmulle]

Elementary lesson exquisitely explained. I can’t imagine anyone getting this wrong (or creating such monstrosities). I guess my imagination is limited at times. ;-)

[@MyOnlineTrainingHub]

:-) Barely a day goes by when someone doesn't share an Excel abomination of some sorts with me, Geert! Hopefully I can help some people avoid these mistakes.

[@GeertDelmulle]

@@MyOnlineTrainingHub It’s really a shame that not all of them are as smart as you. :-) They should invest more into learning how to model. It’s more than just learn to write formulas.

[@ckliesh1]

Thank you for sharing.. How can I create a formula for the following: IF data in cell A1 reads SAW-TCH-TIS, how do I have cell B1 equal cell A1, minus the first 4 letters or numbers? For example cell A1 reads SAW-TCH-TIS. Cell B2 should read TCH-TIS. I have 6,000 rows of data that I must remove the first 3 letters and the dash. Any assistnace would be greatly appreciated

[@MyOnlineTrainingHub]

Hi Catrina, you didn't mention what you want returned if the cell doesn't contain SAW-TCH-TIS so the formula below returns a blank. If you want something else , you can replace the double quotes in the last argument: =IF(A1="SAW-TCH-TIS",RIGHT(A1,7),"")

[@djl8710]

Brilliant as usual.

[@MyOnlineTrainingHub]

Thank you 😊

[@aviman2010]

Quality tutorial as ever. 21 nested IFs? Might make perfect sense at the time of writing but any more than 24 hours later and it would be a nightmare.

[@MyOnlineTrainingHub]

😂 24 hours later..exactly, Tony!

[@cherylrb00]

❤️ Help!!! If I am wanting to get a sum difference of two numbers then if it’s greater or less than 5 points am I able to do so? For example I have a spreadsheet with column one data and column two data. I then subtract the two columns to get a total but if it’s 5 more than the total or 5 less than the total I want to flag it in some way. Is this possible? I’ve only seen it done with percentages but I’m want the actual number. Thank you for all you do!! ❤️

[@MyOnlineTrainingHub]

Yes, it's possible. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@sideshowbobby71]

All great tips.

[@MyOnlineTrainingHub]

Thanks, Bobby!

[@overdraftracing]

*threadjack* I was interested to see that for your xlookup you selected the actual data in the table rather than the whole column via the down arrow you get by hovering on the column header. Was that just personal preference or is there an advantage to doing it that way? I'm usually doing lookups on long tables where grabbing the data would be a bit more work... PS. Thank you for IFS... I had no idea that existed!

[@MyOnlineTrainingHub]

Great question! With small tables I find it quicker to just select the data because I find getting the mouse position exactly right to select the whole table hit and miss. If I'm working with a big table I'll sometimes use the mouse, but quite often I'll got to the ever reliable keyboard shortcut of click one cell then CTRL+A.

[@stefanleys6095]

Hallo Mynda and all here. I was struggling with an If and formula today and cannot solve it properly… it is as follows: I want to calculate the salary cost per year of employees until they go on pension. My available data are as follows: dates people will be go on pension and their salary cost in a specific year + 2% every next year. I have the salary cost per employee and their retirement date (for example: 15/05/2023) coloms and the years 2019 till 2050 in a row (title). At the bottom of my data I added 2 rows as follows: first row starts with 01/01/2019 and second row starts with 31/12/2019. This so I can calculate the cost for several months when not a full year. For example : retirement date here above = 15/05/2023 so the total cost for this employee in 2023 will be 15/05/2023 minus 01/01/2023 x (salary cost x 1,02 index)/365 days. However I want one formula so that it doesn’t calculate the salary cost as soon as the retirement date is surpassed, but still calculate the salary cost when retirement date has not been reached yet… here lies my issue that I can only achieve one condition and not both in one formula :( I need this one formula so that when I change the retirement date the cost is automatically adjusted all years correctly. If I am not mistaken I had this formula: IF(AND(condition 1;condition2);value true;0). Condition 1 and 2 are both to do with the dates here above, but I cannot get it right… or I end up with huge amounts vertically or I end up with negative amounts horizontally etc. I just cannot figure it out 🤯🤯🤯🤕 If you Mynda or anyone else could help me with this enigma then I would be very grateful… My table simplified: Names - retirement date - salary cost 2019 - 2019 - 2020 - 2021 - …. - 2050 Name 1 - 02/04/2019 - 55.000 eur Name 2 - 04/07/2020 - 70.000 eur ••• Name 71 - 14/09/2034 - 116.000 eur ••• Result should all be amounts in 2019. In colom 2020 the values of the employees who retired in 2019 should be zero and amounts vertically and horizontally till you only have amounts in colom with year 2050 for those employees who retire in 2050. So as I said I would be very grateful if someone could help me out on this one please 😵‍💫

[@MyOnlineTrainingHub]

Hi Stefan, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum It's too much to read and keep in my imagination all at once :-)

[@nancynocdvel6620]

Hi Mynda, this was a great video.. I am new at using the IF function and VLookup. I am trying to do something similar but having difficulties. I have created a file to track my stock portfolio and I want to create an easy dashboard as a summary. However, I want the dashboard to be updated as I make trades. For example, I enter the details of a trade in a column, the I want the premium to go into the dashboard on another sheet. However, since I want the summary/dashboard to auto populate as I make these entries, I need the formulas to be something like, if the premium on the stock transaction page was received in January, it should go to the dashboard page in XX cell, or if it was February it would go in the next cell below, etc. I cannot seem to figure out how to do this. Do you have a video that might help clarify for me what to do in this case? Thank you in advance.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nancynocdvel6620]

@@MyOnlineTrainingHub I have now done so. I had several issues trying to get it posted. Thank you very much.

[@RA-rh5lb]

i have this business scenario where ( if ( or (Purchase order qty = Invoice qty received , Purchase order qty < Invoice qty received), " invoice 1", "Nothing" 2. if (And (Purchase order qty = Invoice qty received, Goods REceived < Invoice qty received) .. i am confused on how to make them in one function usine OR/AND or AND/OR .. I wrote like this =IF(OR(A2=C2,A2

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@shoppersdream]

Nice, thanks!

[@MyOnlineTrainingHub]

Thank you!

[@watcher4887]

Hi Mynda, Hope you are doing just great! I am working on an assignment, in my sheet I have to make some data in the K column by using the data in the B column and the blank cells are in the same order in both the B and K columns. The problem is when I use the formula =IF(B10="","",P$4*B3+(1-P$4)*K3) the logical part of the formula is correct (I mean the result is filled in the correct cell or I must say IF part is working ) but the calculation part is picking wrong cells for the calculation I mean P$4*B3+(1-P$4)*K3) Band K cells are wrongly picked up resulting correct cell but wrong data. can you suggest anything for this, please? hope you have time ..thnx for checking...Siraj(India)

[@MyOnlineTrainingHub]

Hi Siraj, Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@watcher4887]

@@MyOnlineTrainingHub Thats really quick Thanks Mynda! i will do that sure.

[@ramzamthel8014]

Thank you so much

[@MyOnlineTrainingHub]

My pleasure 😊

[@tanveerabbas3271]

you master blaster..❤

[@MyOnlineTrainingHub]

Thanks for watching!

[@michaelomosebi6374]

Excellente 🙌

[@MyOnlineTrainingHub]

Cheers, Michael!

[@Go-Odo-publishing]

What if you want to only get data for 4 months and then after a yr start adding data again monthly? Like accruing monthly hours for time off

[@chriscarby13]

If does not equal to as one of the conditions

[@Go-Odo-publishing]

@@chriscarby13 thank you.

[@tilda15un]

how were you able to display the formula to the right of the table?

[@MyOnlineTrainingHub]

Using the FORMULATEXT function.

[@RA-rh5lb]

i didn't understand what exactly you were stating in the record 3.5 where you said lastly because there's no value of false.. could you please explain in layman's language.. thanks in advance ..

[@MyOnlineTrainingHub]

The IF function has 3 arguments: logical test, value if the logical test is true, value if the logical test is false. However, the IFS function doesn't have the value if the logical test is false argument, it just has pairs of tests and value if logical test is true arguments. Therefore, you if do all your logical tests and 'value if logical tests are true' arguments, and they're all false the formula will return an error. Alternatively, to return a value instead, simply enter TRUE in the last logical test (i.e. everything else was false, so this next value if true should be returned), and this will return the last value if true argument, which is effectively the value to return if false equivalent in the IF function.

[@RA-rh5lb]

@@MyOnlineTrainingHub thanks for your kind response

[@moathmustafa9273]

If i Want to make loan schedule with 2 frequency end of month and mid of 3,6,9,12? What we can do?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@moathmustafa9273]

@@MyOnlineTrainingHub could you please send to me your email so i can send attachment? 🙂

[@randomshortvideosytchannel4865]

thank you

[@MyOnlineTrainingHub]

You're welcome 😊

[@alializadeh8195]

Thanks

[@MyOnlineTrainingHub]

You're welcome, Ali!

[@jrvega79]

MUY UTIL

[@MyOnlineTrainingHub]

Gracias!

[@irgski]

What happened to CHOOSE?

[@MyOnlineTrainingHub]

It's not an IF function ;-) This video is about comparing IF functions.

[@mbalendomwafulirwa8048]

the last part of the video is faint abit....but good one

[@MyOnlineTrainingHub]

Not sure what you mean by 'faint abit', but glad you liked the video :-)

[@realpulsecoin]

Switch function 🤔

[@MyOnlineTrainingHub]

:-) Yes! I've written about SWITCH available in Excel 2019 onward (www.myonlinetraininghub.com/excel-switch-function), but this video was dedicated to the IF family :-) Maybe I'll do a video on SWITCH next!

[@realpulsecoin]

@@MyOnlineTrainingHub Youre great! 👏

[@hubisanOne]

Looks like 'IFS' is evaluating all formulas even if a condition is false whereas 'IF' does not. Got the lastest Excel 365. Evaluate this formula '=IFS(FALSE,(3 + 3) + (3 + 3), TRUE, (3 + 3) + (3 + 3))'. Not using 'IFS' anymore because of this. Looks like 'SWITCH' has the same problem.

[@MyOnlineTrainingHub]

Hi Hubisan, I'm not sure the evaluate formula tool is a true representation of how the function is actually calculating in the background. To be sure you'd have to run a speed test on IFS vs IF.

[@hubisanOne]

@@MyOnlineTrainingHub I've done some speeds tests using VBA. 'IFS' definitely evaluates each formula even if a condition is false. If there are for instance four nested 'IF' with only the last being true the 'IFS' formula takes four times as long as the nested 'IF' version. 'IFS' even evaluates all formulas when the first condition is true. It's also noticable without using VBA by just inserting a nested IF with some complex calculations and then inserting the IFS version with the same conditions and formulas.

[@radiobanc]

If you need nested IFs in Excel, it's time to move to a database

[@MyOnlineTrainingHub]

Not necessarily.

[@clickbisi7016]

Use switch function instead, thank me later :)

[@MyOnlineTrainingHub]

:-) Yes! I've written about SWITCH available in Excel 2019 onward (www.myonlinetraininghub.com/excel-switch-function), but this video was dedicated to the IF family :-) Maybe I'll do a video on SWITCH next!