 |
|
|
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 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
· 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 for 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
· 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.