4 Fantastic Alternatives to Nested IF Formulas

Рет қаралды 81,268

Күн бұрын

In this video, we look at 4 fantastic alternatives to nested IF formulas in Excel.
Nested IF formulas are very useful, but they can also be long, messy and convoluted. This video uncovers 4 more effective formulas.
Master more than 150 of the best functions in Excel
amzn.to/3Rg87Go
This video covers the IFS function, lookups and also the relatively unknown CHOOSE function. A demonstration is given of each with comparisons to the nested IF alternative.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Пікірлер: 81

@sharonsfella 2 жыл бұрын

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

@lynxwomancat 6 жыл бұрын

Thanks for the Choose Function demo. That is awesome. I really enjoy your videos, concise and packed full of great information.

@Computergaga 6 жыл бұрын

Thanks Sandy, that is very nice to hear.

@suvarnagadekar356 6 жыл бұрын

So much of useful CHOOSE function shown here. Great !!! Thank you .

@Computergaga 6 жыл бұрын

My pleasure Suvarna.

@carlosalfonso7504 6 жыл бұрын

Fantastic lessons here, Thank you very much!!!

@Computergaga 6 жыл бұрын

Thank you Carlos.

@knz9701 6 жыл бұрын

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. =)

@Computergaga 6 жыл бұрын

You're welcome knz9701. Thank you for the comments.

@viktoriiaovcharova3479 5 жыл бұрын

it is a really great and informative video! thanks a lot for sharing!

@Computergaga 5 жыл бұрын

You're very welcome Viktoriia.

@craigjonker2988 3 жыл бұрын

Thank you, gosh I have been struggling with this for a week now and your solution solved my problem

@Computergaga 3 жыл бұрын

Excellent! Great to hear, Craig.

@studentofthegame2509 4 жыл бұрын

Great, thanks a ton Sir!! Learnt useful stuff!!!!+

@Computergaga 4 жыл бұрын

Excellent! Thank you Rahul.

@EaKaXavier Жыл бұрын

Dear Sir ! GOD Bless You !!!

@Computergaga Жыл бұрын

Thank you 😊

@purnadi21 5 жыл бұрын

Thank you very much Sir for tips VLOOKUP function. it's really help me to count real profit my daily sales from many products.

@Computergaga 5 жыл бұрын

You're very welcome Purnadi. Thanks.

@najwaelmagboul 2 жыл бұрын

Brilliant, thanks for your help

@Computergaga 2 жыл бұрын

You're welcome, Najwa.

@senpaitama8778 4 жыл бұрын

Thank you so much! I was about to cry from my assignment but I got it now!

@Computergaga 4 жыл бұрын

Great job! My pleasure to help Senpaitama.

@alfredzapanta5828 4 жыл бұрын

thank you so much you are the only one who solve my problem thanks!!!!!

@Computergaga 4 жыл бұрын

You're welcome. Thanks Alfred.

@reachhari 6 жыл бұрын

Hey Nice video. Thanks for sharing

@Computergaga 6 жыл бұрын

You're welcome Hari, thanks.

@fomoterapi 6 жыл бұрын

Brilliant !

@Computergaga 6 жыл бұрын

Thank you.

@adrivoss6349 3 жыл бұрын

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.

@Computergaga 3 жыл бұрын

No worries, Adri. Happy to help.

@JoeMontgomeryCPA 4 жыл бұрын

Great vid!

@Computergaga 4 жыл бұрын

Thank you Joe.

@distrilanalytics 3 жыл бұрын

Great Work.

@Computergaga 3 жыл бұрын

Thank you, Daniyal.

@MiWolverine96 5 жыл бұрын

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!!!

@Computergaga 5 жыл бұрын

You're very welcome. Thank you.

@gmkhokhar 2 жыл бұрын

Very nicely explained. Thanks sir

@Computergaga 2 жыл бұрын

You're welcome, Ghulam.

@user-bc3ql3bd2s 5 жыл бұрын

can we have this workbook to download?

@SyedMuzammilMahasanShahi 6 жыл бұрын

Wow. Great video

@Computergaga 6 жыл бұрын

Thanks Syed.

@SyedMuzammilMahasanShahi 6 жыл бұрын

Welcome mate

@SIFYWAY 5 жыл бұрын

Thanks a lot

@Computergaga 5 жыл бұрын

You're welcome.

@cliveburt2638 4 жыл бұрын

Hi great video, will need to lookup how to do drop downs

@Computergaga 4 жыл бұрын

Thank you Clive. Data > Data Validation - sure, go check it out.

@thelegalmirror2196 5 жыл бұрын

Vary helpful

@Computergaga 5 жыл бұрын

Thank you Deepak.

@ipjsinstitute6004 4 жыл бұрын

very good

@Computergaga 4 жыл бұрын

Thank you very much.

@get280 3 жыл бұрын

thank u

@Computergaga 3 жыл бұрын

You're welcome, Karthi.

@willemmulder7304 6 жыл бұрын

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

@Computergaga 6 жыл бұрын

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)

@saleemjco1278 2 жыл бұрын

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

@rockguitarist8907 6 жыл бұрын

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.

@Computergaga 6 жыл бұрын

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.

@bibekkoirala5294 2 жыл бұрын

which formula did you have use in A9 and how you make relation A9 with A10?

@Computergaga 2 жыл бұрын

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.

@wloppers 6 жыл бұрын

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.

@Computergaga 6 жыл бұрын

The limit is 127 tests. But if you get anywhere near that deep, there must be a better way :)

@81wigglez 6 жыл бұрын

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

@81wigglez 6 жыл бұрын

Ive even tried with the AND function

@Computergaga 6 жыл бұрын

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 - kzfaq.info/get/bejne/mspyfKiZ19-4p4E.html Thank you for your nice comments on my videos.

@matpane60 6 жыл бұрын

sweeeeeet

@Computergaga 6 жыл бұрын

Thanks Maxxx

@usmaniqbal1836 3 жыл бұрын

Sr, amazing plz share this file...plz

@Computergaga 3 жыл бұрын

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.

@usmaniqbal1836 3 жыл бұрын

@@Computergaga Ok fine Sr

@kellyoku 4 жыл бұрын

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?

@Computergaga 4 жыл бұрын

Might be the bracket at the start of the formula. Do you get a specific error?

@Computergaga 4 жыл бұрын

Apart from the bracket at the beginning, it looks good. So other thing might be to use ; instead of ,

@victorconradschuster 6 жыл бұрын

✌️

@Computergaga 6 жыл бұрын

Thanks Victor.

@larsholck 4 жыл бұрын

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.

@heenaalmel7510 3 жыл бұрын

Plz Hindi me batao nested if

@Muscleduck 4 жыл бұрын

What, a LOOKUP? INDEX.MATCH is the right way to do it.

@Computergaga 4 жыл бұрын

😀

@FishFarming0 6 жыл бұрын

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