Excel SWITCH Function, is it really as good as they say? You might be surprised! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Peter
Hi Mynda
I did wonder why you had singled out SWITCH for omission!
On closer examination, I tend to agree with you that the function is somewhat underwhelming.
In its favour, it will return Tables from different Sheets when you pair up the table name as text to the name as a Table reference. That is better than INDIRECT, I think, but CHOOSE will provide similar functionality.
Given that the expression and the values it may take are each split between two parameters, like the SUMIFS family, I wonder why ->90- is not accepted as the other part of the condition? It is valid but only as a literal string.
Your reversed lookup of values that yield TRUE was clever, if a tad cumbersome. As and alternative, 3 conditions can be covered by using SIGN
= SWITCH(-
SIGN( TODAY()-[-[Loan Date]] -90 ),-
1, -Overdue-,-
-1, -Not Due-,-
-Due-)
Thank you for the article, even if it did prompt me to spend a couple of hours down a rabbit hole that I otherwise might not have visited!
reply
Hi Mynda
I did wonder why you had singled out SWITCH for omission!
On closer examination, I tend to agree with you that the function is somewhat underwhelming.
In its favour, it will return Tables from different Sheets when you pair up the table name as text to the name as a Table reference. That is better than INDIRECT, I think, but CHOOSE will provide similar functionality.
Given that the expression and the values it may take are each split between two parameters, like the SUMIFS family, I wonder why ->90- is not accepted as the other part of the condition? It is valid but only as a literal string.
Your reversed lookup of values that yield TRUE was clever, if a tad cumbersome. As and alternative, 3 conditions can be covered by using SIGN
= SWITCH(-
SIGN( TODAY()-[-[Loan Date]] -90 ),-
1, -Overdue-,-
-1, -Not Due-,-
-Due-)
Thank you for the article, even if it did prompt me to spend a couple of hours down a rabbit hole that I otherwise might not have visited!
reply
Joseph
Have you ever considered creating a Concat() cell that accepts list information and formats it into an equation that you can just copy/paste into another cell?
For instance a range of cells contains your city/country information, and a cell concats all threat information with the proper punctuation for a an expression. Now you can dynamically change the contents of an expression or create new ones without typing everything out like you did at 1:10 .
Additionally, it helps for trouble shooting or modification for other expressions.
reply
Have you ever considered creating a Concat() cell that accepts list information and formats it into an equation that you can just copy/paste into another cell?
For instance a range of cells contains your city/country information, and a cell concats all threat information with the proper punctuation for a an expression. Now you can dynamically change the contents of an expression or create new ones without typing everything out like you did at 1:10 .
Additionally, it helps for trouble shooting or modification for other expressions.
reply
vicky
Amazing.
Dear Ma'am,
I have an issue. Let me try to explain that:
Rohit(A1) Sharma (B1) 300(C1)
Rohit(A2) Kumar(B2) 200(C2)
I want a cell where I only need (C) value which match with Rohit and Kumar.
Could you please help me?
Regards
Vikas
91-9530802535
reply
Amazing.
Dear Ma'am,
I have an issue. Let me try to explain that:
Rohit(A1) Sharma (B1) 300(C1)
Rohit(A2) Kumar(B2) 200(C2)
I want a cell where I only need (C) value which match with Rohit and Kumar.
Could you please help me?
Regards
Vikas
91-9530802535
reply
Enny
I use the SWITCH function combined with EOMONTH to return the last day of the quater for any given date. In this case SWITCH makes sense. It's only 12 values and they will never change. But most of the time a separate lookup table makes more sense.
reply
I use the SWITCH function combined with EOMONTH to return the last day of the quater for any given date. In this case SWITCH makes sense. It's only 12 values and they will never change. But most of the time a separate lookup table makes more sense.
reply
Deniz
Thank you for the marvelous video, Mynda.
One tiny remark: Just like the lovely -TEXTJOIN()- function, the adorable -SWITCH()- function DNE in Excel 2016, either. They are available only in EXCEL 2019 and EXCEL 365.
reply
Thank you for the marvelous video, Mynda.
One tiny remark: Just like the lovely -TEXTJOIN()- function, the adorable -SWITCH()- function DNE in Excel 2016, either. They are available only in EXCEL 2019 and EXCEL 365.
reply
Brian
This is may be accurate for non technical users of excel. But if you build financial models or are dealing with large records sets then switch is WAY faster than a lookup and can save you huge amounts of time.
reply
This is may be accurate for non technical users of excel. But if you build financial models or are dealing with large records sets then switch is WAY faster than a lookup and can save you huge amounts of time.
reply
Parth
Hi, I want to ask a question on which function should i use for the below scenario: I have fraction data between 0 to 8 in 5 columns. I want to assign a value on basis of range. Let say number = N. If N
reply
Hi, I want to ask a question on which function should i use for the below scenario: I have fraction data between 0 to 8 in 5 columns. I want to assign a value on basis of range. Let say number = N. If N
reply
Jawed
Hi there. I get the point that it is not replacing currently available functions. However, I would like to know Switch is better in any circumstances other than the fact that it is very handy in DAX.
reply
Hi there. I get the point that it is not replacing currently available functions. However, I would like to know Switch is better in any circumstances other than the fact that it is very handy in DAX.
reply
Vijay
Thank you Mynda! It became clear as to, if we should -SWITCH- to this new function or -CHOOSE- the existing ones! Nice presentation as usual! BTW, liked the TRUE parameter logic in SWITCH! --
reply
Thank you Mynda! It became clear as to, if we should -SWITCH- to this new function or -CHOOSE- the existing ones! Nice presentation as usual! BTW, liked the TRUE parameter logic in SWITCH! --
reply
aleksandar40666
its very useful to have this analysis (compare), to show quickly if its useful for daily business or not. Thx alot to you Mynda to save us time. which is today very precious.
reply
its very useful to have this analysis (compare), to show quickly if its useful for daily business or not. Thx alot to you Mynda to save us time. which is today very precious.
reply
Add a review, comment
Other channel videos
