i donrt remember when was the last time i used if in excel, for example the application mentioned here can be better managed by creating a separate table of master data for discount values and then use a lookup formula.
Because their application is very narrow. Just use a lookup table and you can use dynamic data if you have multiple values to match. If you need branching logic then you'll probably end up with nested IF functions anyway. (and maybe at that point using something other than Excel)
for those of us that learned spread sheet (pre MS Office) - in Visi-Calc, we didn't have the IF function, we had the lookup function to use. So many people want to assign letter grades you know the old scale 90-100 = A. 80-89 = B..... and so on, the lookup function is perfect for this scenario. Nested IF functions are a mess and really should be avoided. too hard to troubleshoot. Great Video again.
For the sake of completeness, I would also give CHOOSE a mention. It wouldn't work with your example since it can't handle text but in some instances it's very handy. The other day I wanted a GROUPBY that will extract the month from a date and then sort them in the correct order. Since there will never be more values added, I used SWITCH.
Awesome tutorial on conditional statment. I finally got the hang of the last default argument in the IFS(). The switch() is smarter, with shorter syntax. The expression to be evaluated is at back stage, and is restricted to logical operator "=", and this results in the formula being less readible and not intuitive! My first encounter with the Vlookup() was immediately taken as alternative to the nested if(), specially when dealing with interval/ranked values. At the end the lookup table solution appears to be undisputable winner from the maintenance stand point.
The third option is smart. I guess I keep commenting about data theory on your videos, Mark, but I think a good lesson to remember is that there is no rule that says you have to accomplish everything in one table, especially not if it means you have a formula filled with magic numbers!
Mark please provide a good explanation on first using FILTER function and on that FILTER function as it will return a table and on that table using SUMIFS function, please advise on that.
If you’re using SUMIfS on the result of a FILTER function, then you’re doing something wrong. I can’t think is a scenario where that would ever been a good idea. I suggest you spend more time understanding FILTER and array behaviour.
@@ExcelOffTheGrid Unfortunately, IFS and SWITCH calculate ALL the conditions/results whereas IF stops calculating after the first TRUE. I discovered this while trying to figure out why my recursive LAMBDA was working with IF but not with IFS.
Excellent video as usual! I'm sharing it and asking all my colleagues to subscribe! Can you show ALT-ENTER when typing your formulas... (spaces too!) That's a game changer for many Excel users. (it was for me, and everyone I've shown it to).
Very good video. And very nice topic to discuss!! A lot of people use if and spend hours to do complicated nested IF fuctions, while really - there are just simple solutions. For me, im basicaly nesting one IF as maximum. And thats mostly for cells that are not part of any table. So i only need result once. In any other case, the lookop table solution is for sure more flexible and easier to do. + You can do xlookap for smaller / higher values too which is one of the greatest options there !!
Great Video !! SWITCH is "locked" to a single value and a single comparison op "=" IFS can be a sequence of independent expressions that are checked for TRUE => any comparison operation, any formula or function that checks for TRUE ✌ (There are tricks tho to use SWITCH as IFS ..=SWITCH(TRUE, ) (Downside of IFS, we can not include an exit condition for a recursive function in IFS, will deliver #NUM error, should be good old regular IF formula)
That is very true. In that scenario, I would revert to the =SWITCH(TRUE,...) option. I didn't know about the recursive issue with IFS - that's quite a niche find. Good work.
Re exit condition , that is why i rarely if ever use it, I'd rather nest or use SWITCH which i tend to use with true by default even when not necessary.
@@williamarthur4801 ✌😉 SWITCH(TRUE, ... or any SWITCH still can not include an exit condition for an recursive function. 3 simple tests for simplest recursive functions that count, define this rec functions: RSW=LAMBDA(n,[i],SWITCH(TRUE,i
Yes. Your lookup value can be a formula. Just make sure that the kind of values you look for are are the same type. LEFT results in a text. If the lookup table has stored them as numbers you will get an #VALUE? error message. What you would need in this case is XLOOKUP(LEFT(A,5)+0,lookup column, return column). +0 (or *1) will convert the result into a number. You could also use the VALUE function.
@@ennykraftthank u for sharing this one. When nesting or manipulation functions, we always need to pay attention not only to the arguments/inputs, but also to the expect result/output (single value, range, data type,.....)
My gripe with IFS is that it evaluates the entire formula, even if the first argument returns TRUE - whereas nested IF statements will stop evaluating as soon as TRUE is encountered. No big deal on most spreadsheets , but can really eat up CPUs on huge sheets with complex logical formulas. My experience, IF is less CPU intensive, and quicker. Thus I've stopped using IFS :(
That's interesting - I've never had spreadsheets so big that I've noticed a significant impact. My first thought is why have you got so many IF functions, sounds like data shaping before loading into Excel might help speed things up.
This is my master sheet where Days Range 31 0-30 61 31 - 60 91 61 - 90 121 91 - 120 120 120 and above i tried xlookup for =XLOOKUP(AC2,Sheet1!A:A,Sheet1!B:B,0) where AC2 HAS VALUE 400 AND i PROVIDED THIS FUNCTION IN AD2 =XLOOKUP(AC2,Sheet1!A:A,Sheet1!B:B,0) AND I GOT 0 THEN I CHANGED THE VALUE OF AC2 120 Number of days Aging bucket 120 120 and above WHAT IF I HAVE A CONDITION WHERE I NEED TO USE >31 0-30 >61 31 - 60 91 61 - 90 121 91 - 120 120 120 and above
i donrt remember when was the last time i used if in excel, for example the application mentioned here can be better managed by creating a separate table of master data for discount values and then use a lookup formula.
Yes, that is a solid method 👍
SWITCH and IFS are 2 underrated functions and sometimes I would say even unknown to many users.
Great explanation Mark.
Thanks Ivan - when IF is so will known, most people won't look for alternative solutions.
Because their application is very narrow. Just use a lookup table and you can use dynamic data if you have multiple values to match. If you need branching logic then you'll probably end up with nested IF functions anyway. (and maybe at that point using something other than Excel)
for those of us that learned spread sheet (pre MS Office) - in Visi-Calc, we didn't have the IF function, we had the lookup function to use. So many people want to assign letter grades you know the old scale 90-100 = A. 80-89 = B..... and so on, the lookup function is perfect for this scenario. Nested IF functions are a mess and really should be avoided. too hard to troubleshoot. Great Video again.
For the sake of completeness, I would also give CHOOSE a mention. It wouldn't work with your example since it can't handle text but in some instances it's very handy. The other day I wanted a GROUPBY that will extract the month from a date and then sort them in the correct order. Since there will never be more values added, I used SWITCH.
Yes, CHOOSE is an option where numbers are involved. But I would probably still revert to SWITCH.
Awesome tutorial on conditional statment. I finally got the hang of the last default argument in the IFS().
The switch() is smarter, with shorter syntax. The expression to be evaluated is at back stage, and is restricted to logical operator "=", and this results in the formula being less readible and not intuitive!
My first encounter with the Vlookup() was immediately taken as alternative to the nested if(), specially when dealing with interval/ranked values.
At the end the lookup table solution appears to be undisputable winner from the maintenance stand point.
The third option is smart. I guess I keep commenting about data theory on your videos, Mark, but I think a good lesson to remember is that there is no rule that says you have to accomplish everything in one table, especially not if it means you have a formula filled with magic numbers!
Nice video Mark!
Thanks! Glad you enjoyed it. 😁
Excellent video! So clearly explained even I understood it! 😁
Thanks, I’m glad it was helpful! 😁
Thanks, Mark. Another informative video. Well Done!!!
Thanks Bill - I’m glad you found it useful.
Big thanks Mark! looking forward for the next informative video.
nice! very useful information.
Mark please provide a good explanation on first using FILTER function and on that FILTER function as it will return a table and on that table using SUMIFS function, please advise on that.
If you’re using SUMIfS on the result of a FILTER function, then you’re doing something wrong. I can’t think is a scenario where that would ever been a good idea.
I suggest you spend more time understanding FILTER and array behaviour.
@@ExcelOffTheGrid Can I use Groupby or Pivotby functions on a result of FILTER instead of SUMIFS? Please advise Mark.
I did it through Groupby using choosecols and filter to get this done correctly.
SWITCH with TRUE is one my favorite combos.
Mine too. 😁
Hi Mark, thank you for sharing this information…
Talking about performance, which scenario do you recommend?
Gracias, saludos.
For performance, I don't have a bit preference either way. If it became an issue, I would try them all and see if it makes any significant difference.
@@ExcelOffTheGrid Unfortunately, IFS and SWITCH calculate ALL the conditions/results whereas IF stops calculating after the first TRUE. I discovered this while trying to figure out why my recursive LAMBDA was working with IF but not with IFS.
CHOOSE is also efficient like IF, it only calculates the item chosen
Excellent video as usual!
I'm sharing it and asking all my colleagues to subscribe!
Can you show ALT-ENTER when typing your formulas... (spaces too!)
That's a game changer for many Excel users. (it was for me, and everyone I've shown it to).
Very good video. And very nice topic to discuss!! A lot of people use if and spend hours to do complicated nested IF fuctions, while really - there are just simple solutions. For me, im basicaly nesting one IF as maximum. And thats mostly for cells that are not part of any table. So i only need result once. In any other case, the lookop table solution is for sure more flexible and easier to do. + You can do xlookap for smaller / higher values too which is one of the greatest options there !!
Thanks a lot
Brilliant❤，thanks Mark
Glad you enjoyed it - Thanks Kebin.
Hi! Mark Please upload a video of LET() in all its glory. Thank you!
Great Xlookup solution. Thanks.!!!😄 Why I didnt think!!, but if you have range values to select de % which recommend? (100 - 200) 10%
XLOOKUP can handle range lookups - so that’s achievable.
@@ExcelOffTheGrid do you have any videos on Xlookup for the range scenarios? I have this doubt too
That parentheses at the end of multiple IF looks like wide smile :))))))))
At least that is one positive.
Or it could be 1 smile, a chin and 8 double chins 😁
Great Video !!
SWITCH is "locked" to a single value and a single comparison op "="
IFS can be a sequence of independent expressions that are checked for TRUE => any comparison operation, any formula or function that checks for TRUE ✌
(There are tricks tho to use SWITCH as IFS ..=SWITCH(TRUE, )
(Downside of IFS, we can not include an exit condition for a recursive function in IFS, will deliver #NUM error, should be good old regular IF formula)
That is very true. In that scenario, I would revert to the =SWITCH(TRUE,...) option.
I didn't know about the recursive issue with IFS - that's quite a niche find. Good work.
Re exit condition , that is why i rarely if ever use it, I'd rather nest or use SWITCH which i tend to use with
true by default even when not necessary.
@@williamarthur4801 ✌😉 SWITCH(TRUE, ... or any SWITCH still can not include an exit condition for an recursive function. 3 simple tests for simplest recursive functions that count, define this rec functions:
RSW=LAMBDA(n,[i],SWITCH(TRUE,i
Impressive!
Thank you! Is it possible to use LEFT function with XLookup as a Lookup Value? I want to retreive only 5 digits not a whole sentence.
Yes. Your lookup value can be a formula. Just make sure that the kind of values you look for are are the same type. LEFT results in a text. If the lookup table has stored them as numbers you will get an #VALUE? error message. What you would need in this case is XLOOKUP(LEFT(A,5)+0,lookup column, return column). +0 (or *1) will convert the result into a number. You could also use the VALUE function.
Thanks for answering. Good thought about numbers vs text - I probably would have forgotten that.
@@ennykraftthank u for sharing this one. When nesting or manipulation functions, we always need to pay attention not only to the arguments/inputs, but also to the expect result/output (single value, range, data type,.....)
Can you do one for power query? Why it didn't use switch already is baffling to me, especially as it's in power bi?
Power Query is tricky as there is no SWITCH function... but there are some interesting options. Maybe I should do a video - good idea.
I would use the LOOKUP function.
LOOKUP requires the list to be in ascending order, so would make me feel a little nervous that I might make an error.
Hello Sir,
Please upload videos on M Language from beginners to Advanced on your channel !
Pls make video xlookup between ranges ...-20 to -10 ; -10 to 0; 0 to 10 ;10 to 20; > 20 like that
Make all sense!
My gripe with IFS is that it evaluates the entire formula, even if the first argument returns TRUE - whereas nested IF statements will stop evaluating as soon as TRUE is encountered.
No big deal on most spreadsheets , but can really eat up CPUs on huge sheets with complex logical formulas.
My experience, IF is less CPU intensive, and quicker. Thus I've stopped using IFS :(
That's interesting - I've never had spreadsheets so big that I've noticed a significant impact.
My first thought is why have you got so many IF functions, sounds like data shaping before loading into Excel might help speed things up.
Im wondering if you accept paid excel solutions requests
Thanks for asking. Not at present; we've got a long course and consultancy backlog, so we're not taking anything else on at the moment.
07:09 Came for alts to nested IF, stayed for the tooltip move. Never seen that in *&"&!£ years of using Excel!
Yep, that little tooltip box which has been annoying you for years is movable. 🤯
❤❤❤
This is my master sheet where Days Range
31 0-30
61 31 - 60
91 61 - 90
121 91 - 120
120 120 and above
i tried xlookup for =XLOOKUP(AC2,Sheet1!A:A,Sheet1!B:B,0) where AC2 HAS VALUE 400 AND i PROVIDED THIS FUNCTION IN AD2 =XLOOKUP(AC2,Sheet1!A:A,Sheet1!B:B,0) AND I GOT 0
THEN I CHANGED THE VALUE OF AC2 120
Number of days Aging bucket
120 120 and above
WHAT IF I HAVE A CONDITION WHERE I NEED TO USE
>31 0-30
>61 31 - 60
91 61 - 90
121 91 - 120
120 120 and above