April 23, 2015
October 5, 2010
I would say that I understand that formula because I understand how each of the functions in it works, or at least I understand the parameters each function needs.
You have to take each function in isolation and understand what it is doing and the data you need to feed into it to make it work.
IF requires a test, then it returns a value if that test is true or false.
The test is ISNUMBER(SEARCH("/",A4)). ISNUMBER checks if a value is a number. What's it checking? Its checking the result of SEARCH("/",A4). What does that mean? It means SEARCH is looking for the / character in cell A4. If it finds / it returns a number, if it doesn't find / it returns the #VALUE error.
So you have to work your way down the 'chain' of embedded functions until you get a specific result from some function, then you have to work your way back up that chain evaluating each functions result based on the first result.
I guess to understand a formula with nested functions you need to know the functions but also need to know a method to evaluate the functions in the formula.
On this page Mynda has a video and she explains how to use tools for understanding and debugging complex formulas
Excel Formulas Not Working? • My Online Training Hub
You can also check these pages for info on functions and formulas
Excel Formulas • My Online Training Hub
Excel Functions Explained in Plain English and Practical Examples (myonlinetraininghub.com)
And consider our courses