i am here to find alternatives to IFS because.... so stupid but my job has a version of excel that doesn't have IFS. I sent word to our IT guys to get a patch for it if they can but might be waiting a long time. In the meantime, need my project to work. I really don't want to write the "nested crap", like a viewer below indicated. SO, this being said, Thank You, Sir for putting this on YT.
OMG!!! First off BRAVO!! I was going mad about the multiple answers. There was a total of 5 different percentages ranges with each having a different set amount. I am very familiar with the vlookup formula, but rarely used it for the "true" side it was always for the "false" side. I was going bonkers with this.. Thanks you soo much for putting this excel video together to explain it correctly. =)
Sir How To Solve This Error Please Help , the specified formula cannot be entered because it uses more levels of mesting than ar allowed in the current file format
Love the last one! But I thought if you left a comma without anything after it for the last part of VLOOKUP, Excel treated that like TRUE and would give you an approximate match. Appears I was wrong though, bc that gives me an EXACT match instead. Hmm.
Yes you are right. The last argument of VLOOKUP is optional and true is the default. I did not need to type True. Don't put the comma on the end if you do this though.
Thank you, Usman. Sorry, but only some of my examples have the file. Usually if it is a large data set. Small ones for formula or chart examples I delete after using.
Hi, is IFS function has a limit in terms of number of logic tests. Lower versions of Excel has a limit of 7 when nesting the function IF. If IFS has no limit, then this must be really good alternative.
I'm having issues with a formula and I can't seem to get it right. Wondering if you could take a look, your videos are great. My problem being that once the value is higher than 30ppl I need to charge a different amount between 31-50 is a rate of $55per, 51-150 is a rate of $53per.... etc=IF(E9=31=51=151=226
I would use the VLOOKUP function in a scenario like that I think Lindsay. Create a table with the number ppl in the first column and value to multiply by in the second column. Then write a VLOOKUP to fetch the right value to multiply by E9 like in this video - th-cam.com/video/ddBIEhryTwM/w-d-xo.html Thank you for your nice comments on my videos.
Very good, but ... Why oh why do you implement a combobox without telling us how it is set up. You force us to look for solution to this function instead of continue with either "Price" or "Grade". That would be a much better demo of CHOOSE.
This is the chart? The Combo Box is linked to cell A9. When you insert a Combo Box, right click it and select Format Control. There is a cell link setting. I set that as A9. So the Combo Box selection is shown in cell A9.
Ek questions hai man lijiye Kisi Ka ruselt hai math me 60,eng-50,sci-80 aur us Ka nam ram ho to hum Excel ke cell me sirf us Ka nam likhe to ruselt Apne aap aa jaye
Brilliant.....I alwsys use nested if with combo's and now i will use the much easier choose function....you learn every day
Thanks for this
Thanks for the Choose Function demo. That is awesome. I really enjoy your videos, concise and packed full of great information.
Thanks Sandy, that is very nice to hear.
i am here to find alternatives to IFS because.... so stupid but my job has a version of excel that doesn't have IFS. I sent word to our IT guys to get a patch for it if they can but might be waiting a long time. In the meantime, need my project to work. I really don't want to write the "nested crap", like a viewer below indicated. SO, this being said, Thank You, Sir for putting this on YT.
No worries, Adri. Happy to help.
Thank you so much! I was about to cry from my assignment but I got it now!
Great job! My pleasure to help Senpaitama.
Dear Sir ! GOD Bless You !!!
Thank you 😊
Thank you, gosh I have been struggling with this for a week now and your solution solved my problem
Excellent! Great to hear, Craig.
OMG!!! First off BRAVO!! I was going mad about the multiple answers. There was a total of 5 different percentages ranges with each having a different set amount. I am very familiar with the vlookup formula, but rarely used it for the "true" side it was always for the "false" side. I was going bonkers with this.. Thanks you soo much for putting this excel video together to explain it correctly. =)
You're welcome knz9701. Thank you for the comments.
Fantastic lessons here, Thank you very much!!!
Thank you Carlos.
Very nicely explained. Thanks sir
You're welcome, Ghulam.
Brilliant, thanks for your help
You're welcome, Najwa.
can we have this workbook to download?
it is a really great and informative video! thanks a lot for sharing!
You're very welcome Viktoriia.
thank you so much you are the only one who solve my problem thanks!!!!!
You're welcome. Thanks Alfred.
I know im late to the party...but damn!! ive been writing column IF 2 for YEARS!! the vlookup instead of nested crap. THANK YOU!!!
You're very welcome. Thank you.
Thank you very much Sir for tips VLOOKUP function. it's really help me to count real profit my daily sales from many products.
You're very welcome Purnadi. Thanks.
Sir How To Solve This Error Please Help ,
the specified formula cannot be entered because it uses more levels of mesting than ar allowed in the current file format
Thanks a lot
You're welcome.
Great Work.
Thank you, Daniyal.
Wow. Great video
Thanks Syed.
Welcome mate
Brilliant !
Thank you.
Hey Nice video. Thanks for sharing
You're welcome Hari, thanks.
very good
Thank you very much.
Hi great video, will need to lookup how to do drop downs
Thank you Clive. Data > Data Validation - sure, go check it out.
thank u
You're welcome, Karthi.
Great, thanks a ton Sir!! Learnt useful stuff!!!!+
Excellent! Thank you Rahul.
Great vid!
Thank you Joe.
Thanks for explaning the Choose function.
Is it possible to use a range? f.e. something like this: =choose(A9,Range("A1:Z1")
KR
Willem
You can use ranges in the CHOOSE function, and the Index Number could be an array constant.
This is an example.
=CHOOSE({1,2},B2:C6,L2:M8)
Love the last one! But I thought if you left a comma without anything after it for the last part of VLOOKUP, Excel treated that like TRUE and would give you an approximate match. Appears I was wrong though, bc that gives me an EXACT match instead. Hmm.
Yes you are right. The last argument of VLOOKUP is optional and true is the default. I did not need to type True. Don't put the comma on the end if you do this though.
Sr, amazing plz share this file...plz
Thank you, Usman. Sorry, but only some of my examples have the file. Usually if it is a large data set. Small ones for formula or chart examples I delete after using.
@@Computergaga Ok fine Sr
Hi, is IFS function has a limit in terms of number of logic tests. Lower versions of Excel has a limit of 7 when nesting the function IF. If IFS has no limit, then this must be really good alternative.
The limit is 127 tests. But if you get anywhere near that deep, there must be a better way :)
Thank you so much, but am having error(=IFS(B2="Platinum",110,B2="Gold",90,B2="Silver",65,B2="Bronze",40) #NAME? can you check for me?
Might be the bracket at the start of the formula. Do you get a specific error?
Apart from the bracket at the beginning, it looks good. So other thing might be to use ; instead of ,
I'm having issues with a formula and I can't seem to get it right. Wondering if you could take a look, your videos are great. My problem being that once the value is higher than 30ppl I need to charge a different amount between 31-50 is a rate of $55per, 51-150 is a rate of $53per.... etc=IF(E9=31=51=151=226
Ive even tried with the AND function
I would use the VLOOKUP function in a scenario like that I think Lindsay.
Create a table with the number ppl in the first column and value to multiply by in the second column.
Then write a VLOOKUP to fetch the right value to multiply by E9 like in this video - th-cam.com/video/ddBIEhryTwM/w-d-xo.html
Thank you for your nice comments on my videos.
Very good, but ...
Why oh why do you implement a combobox without telling us how it is set up. You force us to look for solution to this function instead of continue with either "Price" or "Grade". That would be a much better demo of CHOOSE.
which formula did you have use in A9 and how you make relation A9 with A10?
This is the chart? The Combo Box is linked to cell A9. When you insert a Combo Box, right click it and select Format Control. There is a cell link setting. I set that as A9. So the Combo Box selection is shown in cell A9.
Plz Hindi me batao nested if
What, a LOOKUP? INDEX.MATCH is the right way to do it.
😀
✌️
Thanks Victor.
Ek questions hai man lijiye Kisi Ka ruselt hai math me 60,eng-50,sci-80 aur us Ka nam ram ho to hum Excel ke cell me sirf us Ka nam likhe to ruselt Apne aap aa jaye
So much of useful CHOOSE function shown here. Great !!! Thank you .
My pleasure Suvarna.
sweeeeeet
Thanks Maxxx
Vary helpful
Thank you Deepak.