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. =)
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.
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.
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.
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
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.
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.
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 - ua-cam.com/video/ddBIEhryTwM/v-deo.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.
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
Very good and easy explanation ! Thanks you !
You are welcome! Thank you very much.
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.
Very nicely explained. Thanks sir
You're welcome, Ghulam.
So much of useful CHOOSE function shown here. Great !!! Thank you .
My pleasure Suvarna.
Brilliant, thanks for your help
You're welcome, Najwa.
Thank you, gosh I have been struggling with this for a week now and your solution solved my problem
Excellent! Great to hear, Craig.
it is a really great and informative video! thanks a lot for sharing!
You're very welcome Viktoriia.
Dear Sir ! GOD Bless You !!!
Thank you 😊
Fantastic lessons here, Thank you very much!!!
Thank you Carlos.
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.
thank you so much you are the only one who solve my problem thanks!!!!!
You're welcome. Thanks Alfred.
Great Work.
Thank you, Daniyal.
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.
Wow. Great video
Thanks Syed.
Welcome mate
Great vid!
Thank you Joe.
Thanks a lot
You're welcome.
Hey Nice video. Thanks for sharing
You're welcome Hari, thanks.
Thank you so much! I was about to cry from my assignment but I got it now!
Great job! My pleasure to help Senpaitama.
Brilliant !
Thank you.
Hi great video, will need to lookup how to do drop downs
Thank you Clive. Data > Data Validation - sure, go check it out.
can we have this workbook to download?
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.
Great, thanks a ton Sir!! Learnt useful stuff!!!!+
Excellent! Thank you Rahul.
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.
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.
very good
Thank you very much.
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
thank u
You're welcome, Karthi.
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)
Vary helpful
Thank you Deepak.
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
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 :)
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.
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 - ua-cam.com/video/ddBIEhryTwM/v-deo.html
Thank you for your nice comments on my videos.
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 ,
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.
What, a LOOKUP? INDEX.MATCH is the right way to do it.
😀
sweeeeeet
Thanks Maxxx
✌️
Thanks Victor.
Plz Hindi me batao nested if
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