Create a Custom Excel Function to Replace Nested IF Functions

Do you get frustrated writing formulas that require that IF Functions and AND Functions be nested inside each other? Most people do get frustrated - especially when they need to make changes to the formula.

Let me show a faster and easier way to solve this problem - I will demonstrate in just over 7 minutes how to create your own custom functions in Excel - to accomodate as many layers of IF statements as you will ever need!

I use the example of writing a formula to grant a different discount per sales order depending upon how many units are ordered. This is a common situation - and it becomes compounded when the sales manager decides to revise the discount tiers!

If you thought that using VBA (Visual Basic) fwas difficult, think again. You can construct this custom function in less than ten minutes, even if you have never recorded a Macro. It is that simple to create!


Danny Rocks
The Company Rocks

[DannyRocksExcels]

@PaperTrailFinancial
Thank you! I am pleased that you enjoyed my Excel Tutorial.

I am trying to "de-mystify" Excel VBA - It is really not that difficult to understand - and, in this case, it is a lot easier than creating long, convoluted, "Nested IF Functions" in an Excel formula.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

@arod363636
Thank you! I am pleased that you took the time to add your comment.
I do think that the "Case Is" Construct in VBA is so much easier to create, edit and understand that the "rat's nest" or "Nested IF() Functions" that many people try to build.

VBA truly is very powerful - and once you spend a little time to understand it, fairly easy to write and use.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

Thank you, I am pleased that you enjoyed my tutorial for creating a custom function in Excel using VBA.

If I understand your question, you simply use this Custom Function just as you would use any other Excel Function - select the cell where you want the function to return the discount and point to the cell that contains the quantity.

If you meant that the Case Is would contain different "tiers, " then you simply write a second custom function. You can keep this in the same module.

Danny Rocks

[DannyRocksExcels]

Thank you for your feedback.

As you have discovered, there are almost always multiple ways to accomplish a goal in Excel. Thank to for offering your suggestion.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

When you first open up VBA, you get a large blank area in the middle. Simply go to the Insert Menu and select Module.

Now you can begin to write your VBA Code.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

@Markblackburn86
Thank you! I appreciate you taking the time to send me your feedback.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

@xzadeh
Thank you very much for your kind words.

I am pleased that I can help you to improve your knowledge of Excel.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

Well I certainly hope that you are now doing "a happy dance!"

In my experience, it take several attempts to correctly write VBA Code. Just like acquiring any new skill, you need to perform repetions in order for this to become a new habit.

Keep us posted with your results!

Danny Rocks
The Company Rocks

[Auktoriserad]

Can also be done by using Vlookup, creating a matrix with columns "from", "to" and "discount" and using "TRUE" for the argument "aproximately".

[DannyRocksExcels]

Hello Hammad -

Sorry for the delay in responding - I missed the notification off your comment.

I have not yet created a video that demonstrates the If - Then - Else procedure in VBA Code. I will do so shortly.

Use the same logic as writing a traditional IF() Function - If(Logical_Test, Result if TRUE, Result if FALSE).

I am pleased that you enjoy my tutorials and teaching style.

Danny Rocks
The Company Rocks

[xzadeh]

Danny truly Rocks!
Your tutorials are absolutely great. Thank for the education.
H. Zadeh

[marcus.t.mister]

Outstanding tutorial. You have saved many obese excel sheets from clogged function arteries.

[jamesperry]

Brilliant !
Like the VBA part.
Thank u Danny

[toniaguilar]

So what happens when I open the VBA and there is no Module option?

[hidgik]

If youtube would have allowed me I would have liked your video about like 12 or 13 times.

[abbgtpopkipog]

Hello Danny!
Thanks for you videos and your way of teaching.
its and easy gateway for us to use excel VBA.

One quick question, how can we can use the else command with the function described if we have values other than particular ones.
Or also please direct me to the link if its already explained elsewhere by you.

Looking forward to hear from you soon!

Team Danny RoCKS!

[Kaotica]

Okay, that was amazing! Thank you. Quick question how do I start a second function, doing basically the same thing just with a different set of values? Do I write directly under it, do I need a new mod?

[vicky]

Hello Danny, its a wonderful video, but I wanted to know in case I have multiple conditions before I get a single value, how would I do it. For instance, taking your example if the Qty - 5 and Location is AA, then discount is 10%, for Qty - 5 and location BB, discount would be 15% and so on. Any help here would be appreciated.

[tyrifthorney]

How do i use the date in the Nested If function like suppose I was aks to determine if someone should get a discount based On the date they purchase good or item

[Auktoriserad]

May I ask you what software you are using to screenrecord your excel tips and time savers?