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).
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!
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.
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.
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
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.
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 !!
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.
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.
@@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.
Hello Sir,
Please upload videos on M Language from beginners to Advanced on your channel !
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.
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).
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!
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.
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.
SWITCH with TRUE is one my favorite combos.
Mine too. 😁
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.
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
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.
Hi! Mark Please upload a video of LET() in all its glory. Thank you!
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.
Pls make video xlookup between ranges ...-20 to -10 ; -10 to 0; 0 to 10 ;10 to 20; > 20 like that
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 !!
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.
Big thanks Mark! looking forward for the next informative video.
Brilliant❤,thanks Mark
Glad you enjoyed it - Thanks Kebin.
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.
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.
❤❤❤
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.
Nice video Mark!
Thanks! Glad you enjoyed it. 😁
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