Writing formulas - general question

General Excel Questions & Answers

Last Post by Alan Ramsay 4 years ago

3 Posts

2 Users

0 Reactions

99 Views

RSS

Alan Ramsay

(@alanr)

Posts: 79

Estimable Member

Topic starter

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

Posted : 11/03/2021 2:28 pm

Philip Treacy

(@philipt)

Posts: 1629

Member Admin

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.myonlinetraininghub.com/advanced-excel-formulas-course

https://www.myonlinetraininghub.com/excel-expert-upgrade

Regards

Phil

Posted : 11/03/2021 7:09 pm

Alan Ramsay