Excel Forum

Writing formulas - general question

Alan Ramsay

Member

Members

Forum Posts: 79

Member Since:
April 23, 2015

Offline

March 11, 2021 - 4:28 am

Hi there,

I have a very general question. Can anyone recommend any approach/process to get comfortable and truly understand the logic of formula's in Excel? I guess a myonlinetraininghub course might be recommended 😉 and I'd consider that for sure but I am often stuck or struggling as I try to write formulas and often I think because I am not fully understanding the logic of the syntax.

For example: =IF(ISNUMBER(SEARCH("/",A4)),TEXT(LEFT(A4,FIND("/",A4)-1) / MID(A4,FIND("/",A4)+1,99),"#.000#"),TEXT(A4,"#0.000#"))

That type of formula, not these functions specifically but this type of "complex" formula that involves multiple functions.

Any and all advice appreciated!

Alan

See Answer

Philip Treacy

Admin

Forum Posts: 1619

Member Since:
October 5, 2010

Offline

March 11, 2021 - 9:09 am

Hi Alan,

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

https://www.myonlinetraininghu.....s-course

https://www.myonlinetraininghu.....rt-upgrade

Regards

Phil