All 3 Excel IF Statements + how to AVOID PERFORMANCE ISSUES!
Вставка
- Опубліковано 25 сер 2024
- IF Statements are essential for all Excel users to master. Unfortunately, many get stuck with nesting and make mistakes that can cause performance issues, which I address in this video.
Download the Excel file here: www.myonlinetr...
Use our IF Formula Builder: www.myonlinetr...
0:27 Excel IF Formulas
1:39 Nested IF Formulas
3:14 Excel IFS Function
4:24 Mistakes that cause performance issues
6:04 VLOOKUP Alternative
7:52 XLOOKUP Alternative
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy
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!
Great to hear, Ivan!
You describe things simple and to the point. Brilliant !!
So pleased you find my tutorials helpful :-)
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!!
😁 Thanks, Wayne!
Hi Mynda!Great Run Through And Explanation Of These Awesome Functions...Thank You :)
Thanks for watching, Darryl!
You always explain Excel topics in such a way anyone can understand. :)
Glad to hear that, Pasco!
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
Thanks so much!
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.
Yes, indeed!
very clever way in how to use dates with tables and vlookup instead of if statements. thanks !
Glad you like it!
Super helpful, thanks Mynda!
Cheers, Chris!
Mynda thank you.
Can you make a tutorial about the DGET function and its sub formulas.
Will add it to my list.
Nice, thanks!
Thank you!
Brilliant as usual.
Thank you 😊
Thank you so much
My pleasure 😊
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
Here's a comprehensive tutorial on XLOOKUP: ua-cam.com/video/2ViMm-wuM3U/v-deo.html
@@MyOnlineTrainingHub thank you
Elementary lesson exquisitely explained. I can’t imagine anyone getting this wrong (or creating such monstrosities).
I guess my imagination is limited at times. ;-)
:-) 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.
@@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.
thank you
You're welcome 😊
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.
😂 24 hours later..exactly, Tony!
All great tips.
Thanks, Bobby!
Thanks
You're welcome, Ali!
MUY UTIL
Gracias!
Excellente 🙌
Cheers, Michael!
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
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),"")
*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!
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.
how were you able to display the formula to the right of the table?
Using the FORMULATEXT function.
❤️ 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!! ❤️
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
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 😵💫
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 :-)
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)
Hi Siraj, Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thats really quick Thanks Mynda! i will do that sure.
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.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I have now done so. I had several issues trying to get it posted. Thank you very much.
If i Want to make loan schedule with 2 frequency end of month and mid of 3,6,9,12? What we can do?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub could you please send to me your email so i can send attachment? 🙂
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
If does not equal to as one of the conditions
@@chriscarby13 thank you.
What happened to CHOOSE?
It's not an IF function ;-) This video is about comparing IF functions.
the last part of the video is faint abit....but good one
Not sure what you mean by 'faint abit', but glad you liked the video :-)
Switch function 🤔
:-) 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!
@@MyOnlineTrainingHub Youre great! 👏
If you need nested IFs in Excel, it's time to move to a database
Not necessarily.
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.
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.
@@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.
Use switch function instead, thank me later :)
:-) 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!