OzGrid

Excel tries to help with formula writing

When you write a formula within Excel it will give you a lot of assistance if you know what to look for! For instance, we know that for every opening parenthesis we must have a closing one. This it not a problem when we only have one formula or have only nested one function within another. However, when you start nesting many formulas or nest formulas within other nested formulas it can get very confusing.

Consider this example:

=IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no")

We know that we have four open parenthesis so we must logically have four closing parenthesis, but which closing parenthesis goes with which open parenthesis? You could take a educated guess and try maybe:

=IF(AND(A1=20,NOT(ISNUMBER(A2)),"yes","no"))

but Excel is not going to like this and display a message box telling us our formula contains an error. This is because we have put our closing parenthesis in the wrong places. Well let's step through and type our formula one bit at a time, but this time we will watch for Excels' leads.

• Select cell B1 and then click in the formula bar.

• Type: =if(and(    If you look closely you should see that the second parenthesis is red.

• Select cell A1 and type: =20,not(   This time our parenthesis should be blue.

• Type isnumber(   Our parenthesis should be brown.

• Select cell A2

Now we have completed putting in all our functions, so we can start closing our parenthesis.

• Watch closely and type ) you should see the parenthesis turn brown and momentarily bold itself along with the parenthesis after isnumber. This is Excel telling you, that you have just closed off the ISNUMBER function.

• Type another ) and it will turn blue and again momentarily bold itself along with the parenthesis after not

• Now type a  ,”yes”, “no”

• Type ) once more and we have now closed off our functions back to the AND.

• Finally, we must always close off any nested function with a closing parenthesis, so we put in our final arguments for the IF function and then close with a ) and it will match up with the open parenthesis after the IF.

Now we can push Enter and it will accept our formula without any problems. We could even leave off the very last parenthesis and Excels' Autocorrect feature would complete it for us.   Another good habit to form is to use lower case when typing the function names. This is because Excel will recognise the function names and capitalize them for us. This comes in handy when you type a long nested formula with lots of different functions, if you misspell a function name Excel will not capitalize it and you will know at a glance where your problem lies. Try it with the above formula, using all lower case and misspell isnumber.

If you are faced with the rather complicated task of having to write a long and/or deeply nested formula, sometimes it pays to break it into 'bite size chunks' first.  Although our nested formula<b.=if(and(a1=20,not(isnumber(a2))),"yes","no")> is by no means a long or deeply nested formula (in the scheme of things) it could be constructed by writing it in 'bite size chunks'. Once we know the conditions we need met before we have our formula return yes we can build our formula in separate cells.</b.=if(and(a1=20,not(isnumber(a2))),"yes","no")>

• Select cell B1 and type =not(isnumber(A2)).

• Highlight the text not(isnumber(A2)) in the formula bar and then right click and select Copy then push Enter.

• Select cell C1 and type =and(A1=20,) now in the formula bar right click immediately after: 20, and before ) and select Paste.

•  You should have: =and(A1,not(isnumber(A2))). Now Copy this from the formula bar, again leaving off the equal sign and push Enter

• In cell D1 type: =if(). Right click between the open and closing parenthesis in the formula bar and select Paste.

• You should now have: =if(and(A1,not(isnumber(A2)))) and the mouse insertion point will be flashing between our second last and last closing parenthesis.

• Type a comma and then type our result if we want if our IF function is TRUE ie; "yes". Type another comma and then our result we want if the IF function is FALSE. Push Enter.

This is a method I use frequently when constructing a complicated or deeply nested formula, it works very well, but as with most things it does take practice.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and

Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples