 |
|
|
|
|
The method I have described in the last lesson it not the simplest way to write a nested formula, but instead is designed to show you what is going on and hopefully help you understand how nesting works.
We strongly suggest you practice writing small to medium nested formulas. This way to force yourself to understand how Excel wants it's nested formulas. Once you have understood this you can move on to a very simple way that will have Excel do all the opening and closing parenthesis for you. We do this with the Insert function. This is where Excel has nearly every single formula that it can accept.
There are three methods we can use to show the Insert Function dialog and which one you use is purely optional. The three methods are:
·
Going to Insert>Function
·
Push Shift + F3
·
Click the Insert Function icon to the
left of your Formula bar (Fx),
or for older version users, click the Paste Function icon on your Standard
toolbar.
Once activated you will see the Insert Function dialog pop up in front of you. Depending on which version of Excel you are using, these heading names may vary slightly in this dialog box.
We will use the Insert Function dialog to help us write the above function.
· Under
Or Select a Category select Logical then under Select a
Function select IF. At the bottom of the dialog you will see a
brief description of what the IF function does.
· Click
OK.
· Make
sure you are clicked in the Logical_test box.
· Ok,
we need the AND function next. To the left of your Formula bar
(where the Name box normally is) you should see the word IF and a
small drop arrow to the right. Click this arrow. You should see a list of ten functions, these will be the last ten functions you have
used. If the function we want is there we can simply select it, otherwise click
More functions... and our Insert function
dialog will come back. Select Logical and then AND.
· Look
in your formula bar and you should see IF(AND())
The AND and it's parenthesis are bolded
because this is our active function.
· Click
the Collapse tool for the Logical1 then select cell A1, then
click the Collapse tool again and type =20 . Now click in the Logical2
box.
· Again
click the small drop arrow to the left of the formula bar. Go to More functions... and this time select
NOT.
Once again click the small drop arrow to the left of the formula bar. Go to More
functions... and this time select Information under
· Click
the Collapse tool and select A2 then click the
· We
now need to get back to our IF function so select the word IF in
the formula bar. Click in the Value_if_true
box and type: Yes. Click in the Value_if_false
box and Excel will automatically place quotations around our Yes. It
does simply because it is text, had it been a number this would not occur.
Type: No and click OK.
We have now written a simple nested function using the Insert Function. The temptation will be there to use this all the time for nested functions, but try to resist for now (on the small ones at least) as you will not get the full gist of what Excel wants. By all means use the it for harder nested functions, but do watch what Excel is doing as you go along. This is the best way to learn. We know many heavy users of Excel that can write very complicated nested formulas if they use the Insert function, but they have no idea what is happening and could not write the same formula without this aid.
This is a word that you will see and hear often in Excel. It simply refers to the order of a functions arguments. For instance the syntax for the IF function is: logical_test,value_if_true,value_if_false. The syntax of any function cannot be altered, however this does not mean we must supply a value for all arguments in every function. Some functions have what is known as optional arguments. This means you can leave them blank. It is important to note though, that if an optional argument is between two arguments, you must still separate each argument with a , (comma).
To see what we mean type the word: Hello in cell A1 and then in any cell type: =substitute(A1,"l",,1) you can see that we have omitted the third argument in this function, but the omitted argument is still between two commas. By doing this we have let Excel know exactly which argument we have omitted. There are not too many functions that will allow this, but you should still be aware of it.
As you become more proficient with writing formulas you may only need a quick reminder of the functions syntax. To have Excel give us the memory jog do this:
· In
any cell type =If
· Now
push Ctrl+Shift+A
In the formula bar you should see : logical_test,value_if_true,value_if_false. Our IF functions syntax.
We use this one a lot when we are writing functions we have not used for sometime. It is usually just enough to jog the memory!!
No matter how good we get at using Excels functions we will at times need to do some troubleshooting with our formulas. Excel will again assist us greatly here if we know what we need to do.
To keep things simple let's assume our =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no") formula is not giving us the result we expected and we don't know why.
· Put
the number 20 in cell A1 and in A2 put ="1"
· In
any cell enter =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no").
· Our
result will be yes. We expected the result no, because A2=20
and A1 is a number. (Ok, it's not really as we enclosed it in ""
(quotations), but we don't know this).
·
Lets step through our formula and see why. With
your formula cell the active cell click on the Fx
symbol to the left of the formula bar
· Click
on the AND in the formula bar and only the AND and it's arguments will be bolded.
· If
we look in the Logical1 and the Logical2 box we will see the
result of the two arguments we supplied. They will both read True.
· We
think Logical2 should read False though as A2 does have a
number. (Remember we have reversed the result by using the NOT function)
· Click
on ISNUMBER in the formula bar. We can now see that the number one is
not really a number at all, it's text. We know this
because we can now see the "" (quotations) around it.
· While
we are here, click back on the
At the very bottom we have Formula result=yes. This is the result of our entire nested formula. The last two steps will apply to any function or formula and again allows us to step through our formula bit by bit.
If the truth be known we should have known immediately where our problem was as the 1 was NOT right aligned in the cell. ALL numbers are right aligned in a cell by default. Text is always left aligned by default. Of course somebody could have overridden the default alignment by formatting the cell. This may be a good time to point out that overriding Excels default alignment is a bad habit to form and we would strongly suggest you don't do this if possible, except maybe for headings.