Additional logical functions

These are NOT part of the AAT synoptic, but I deem them sufficiently useful that it warrants going over them – particularly how to hide an error message. However, if you only want to learn what you need for the exam and no more, then click here to continue.

If you’re curious, then please, dear reader, come follow… caution: mind-altering formulae ahead!

Hiding error messages with IFERROR

This is a fantastically useful function. I use it a lot for spreadsheets I’m designing for others to use. Basically, it hides error messages (anything that starts #, like #N/A or #DIV/0!) so your users don’t get freaked out – and of course there’s always the thing that it looks kind of neater too.

Consider this example here, where having entered a name which isn’t on the list, my VLOOKUP has returned a #N/A in cell B2. It would be very useful to be able to say something like ‘no-one of that name’ or even not show anything at all.

What we need to do is use the IFERROR function. Let’s have a look at it.

=IFERROR ( value , value_if_error )

It seems simple enough – only two arguments – but it can confuse people a little bit. What it’s saying is If that’s an error, do this instead. We can work through it like this: The first argument, value, is the formula that’s generating the error. That’s right, the formula in its entirety is going to be our first argument. The second argument, value_if_error, is what we want to happen if indeed there is an error – have a useful message or maybe not show anything at all.

That will mean my function will look like:

=IFERROR ( VLOOKUP(B1,A5:B9,2,0) ,“no-one of that name” )

Now I needn’t worry about the #N/A as it’s all taken care of.

The IS functions

There are nine functions in the IS group, and they all return TRUE or FALSE. They are most commonly used to check for errors in data entry or as the result of formulae. For instance, if cell A1 appeared to have a numeric value in it, you could check by saying =ISNUMBER(A1). If A1 is a number, it would return TRUE. If it wasn’t, it would return FALSE.

You can harness these by having them as your first argument in an IF statement. Supposing you want to multiply cell B2 by the VAT rate, 20%. However, that’s going to fail if someone puts a text value in B2, instead of a numeric value. So, we could say:

=IF ( ISNUMBER (B2) , B2*20% , “please enter a number“)
If it’s true that B2 is numeric then multiply it by 20%, otherwise display the text “please enter a number”.

The complete family of IS functions comprise ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT.

– ISNA and ISREF test specifically for the presence of #N/A and #REF errors respectively.
– ISERROR tests for an error – any error.
– ISERR tests for any error but #N/A.

AND and OR

Here are a couple of functions which can totally blow your IF statement wide open. These two functions are really simple and hugely widen the scope of an IF.

• AND looks at various conditions and if ALL are true, returns TRUE. If just a single condition is not true, it returns FALSE.

In the example below, all my conditions are that the three values are above 30. All of them are; so we have an answer of TRUE.

In this example, one of my conditions is now below 30. This causes AND to return FALSE.

• OR looks at various conditions and if just ONE is true, returns TRUE. If no conditions are true, it returns FALSE.

In the example below, my conditions are that the three values are above 30. Even though one of them isn’t, it doesn’t matter – at least one of them is so we have an answer of TRUE.

In this example, none of my conditions is now above 30. This causes OR to return FALSE.

• How do we use these? We nest them as the first argument of an IF (logical_test). As an example, supposing in a qualification you only get a certificate if you pass five exams, not just one? We would need an AND in order to test that all cells concerned have values above 70.

In the first example here, our candidate failed Exam 5, so doesn’t get a certificate:

Our second candidate has passed all five, so we can award them their certificate.

Note how the whole of the logical_test is the AND function:

Exciting formula ahead!

So… you’re wondering what you’d do if you had to go off the Result, not the Score? This calls for a bit of a stop-and-think…. I need to check:

• that C2 says either “distinction, “merit” or “pass” AND
• that C3 says either “distinction, “merit” or “pass” AND
• that C4 says either “distinction, “merit” or “pass” AND
• C5 AND C6 etc….

so I need ORs and an AND. And I need my ORs inside – nested in – my AND. Check this out. This is fun!

=IF(AND(OR(C2="distinction",C2="merit",C2="pass"),OR(C3="distinction",C3="merit",C3="pass"),OR(C4="distinction",C4="merit",C4="pass"),OR(C5="distinction",C5="merit",C5="pass"),OR(C6="distinction",C6="merit",C6="pass")),"Award certificate","no certificate")

Break it down. Look at it carefully. Count the open and close brackets – a pair each for each OR, a pair for the AND, a pair for the IF. Count the commas – one to separate each OR in the AND, one to separate the AND from the other two arguments of the IF. I’m certain you’ll find that if you’re really careful, you actually can follow the logic of what’s going on. I’ve coloured the different arguments here so you can see more easily what’s happening.

Wow, right? How cool is that? And it’s *mind-blowing* that you can work your way through it and create something like this yourself!

It really is a case of looking at what’s going on with a critical, careful eye and working your way logically through the symbols. After all, at this point, you know that any time you see Word( you know you’re looking at a function. You can have a look at the Help files in Excel and understand a lot more of what it’s explaining about that function – you know what syntax, arguments, cells, values, ranges and so on are. You understand the $ and the “double quotes”. There are only a couple of other symbols you should learn (covered in this post) and you can genuinely look at anything anyone has created and work out what’s going on.

I appreciate I went way off-piste on this, in terms of what you need for the synoptic, but I hope you now appreciate why I wanted to go through this. The power of all these functions combined is truly, truly staggering – and you have the key to it all. Enjoy!

Have a play

Please feel free to download some exercises here – lots of worksheets for you to get your teeth into. If you’re feeling brave, recreate the nested IF/AND/OR above!

If you’re following the suggested Learning Plan, please click here to go to the next post.

The SUBTOTAL function

If you haven’t had a look at doing Automatic Subtotals, then you might find it useful to have a look at that first.

Ready to get started?

SUBTOTAL syntax

Woah, woah, woah… syn-what? If you have no idea what I’m talking about, have a quick read of this post here!

The syntax is SUBTOTAL(function_num,ref1,[ref2],…) and the arguments are as follows:

• function_num: this refers to exactly which function you want to apply to your subtotal. This was done for you at the second step of your Subtotal dialog box. Each specific number refers to a function. 1 = AVERAGE, 2 = COUNT, 3 = COUNTA and so on. They’re in alphabetical order, usefully!
• ref1: this refers to the range or cell you’re applying the subtotal to.
• [ref2]: remember, anything in square brackets [ ] is an optional argument. So if you have any other cells or ranges to add in, do so here. You can see this in the demonstration gif below.

The function_num argument

When you type out your SUBTOTAL, as soon as you type that open bracket you’ll get a handy drop-down to let you know which function you need to pick. So you don’t need to remember them!

(Please don’t ask me what STDEV.S, STEV.P, VAR.S or VAR.P do. They’re to do with Standard Deviation and Variance, both of which are statistical functions. I’ve never had to use them and – at Level 3 at any rate – neither will you!)

There are two ways of doing this. In the image above, you can see it says 1 – AVERAGE and then further down 101 – AVERAGE – what’s the difference?

• Any function in the 1-9 series works by ignoring Filtered data.
• Any function in the 101-109 series ignores rows which are manually hidden.

So it depends how your data has been presented. Take a look at this to see an example of both in action – keep your eye on the value in cell C32 as I hide the rows.

How cool is that?

Of course, you can see it has disadvantages, in that you have to manually Hide your rows each time.

In the exam

I confess I’ve yet to do an exam, but I’ve done one of the AAT mocks, and it asks you to write a Subtotal formula after you’ve applied a Filter – in which case, for your first argument you’ll be choosing from numbers 1 to 9 only.

In the example below, I’ve added Filter buttons and amended the first Subtotal function so both are showing the same range of cells: C1:C30. At first, both show a full SUM of the data. Once I apply the filter to show Word only, you can see both show the same answer. Remember, the second type (101-109) only really applies with manually hidden rows, so in this case, you can see you won’t have to worry about which type you use!

You can see with the second argument, C1:C30, I’ve included the first row, which has a text value (the word Sales). Of course, any function which works on numbers is just going to ignore text values, so it really wouldn’t matter if I did C1:C30 or C2:C30.

Examiner’s Report

I just came across the following in the Examiner’s Report: “Students often take considerable time over the task but do not follow the specific requirements, for example, to use Subtotal (not just Sum) […]” Therefore, if you see a specific requirement to do a Subtotal, do a Subtotal.

Data Table Subtotals

It could be argued that using the Totals Row feature in Data Tables, as mentioned here, won’t comply with a specific requirement to ‘use a Subtotal formula’. I’d steer clear in this instance and type one manually (though there’s nothing to stop you from formatting the data as a table!).

A couple of notes about SUBTOTAL

It may be that I’ve applied my filter and then I realise I want to add a Subtotal, as per the mock I referred to above. Looking at the official answer from the mock, the range excludes some of the rows because they’ve already been filtered out. So, don’t feel obliged to un-Filter, write your SUBTOTAL referring to the whole range, and then re-Filter. No point! Besides, I doubt you’ll be wanting to waste the time if you do this in the actual exam, right?

This function will only work if you have columns of data. It can only work ‘vertically’.

It can only work on a single worksheet. If you’re trying to subtotal data spread over several worksheets together, it won’t work.

Have a go

In the previous post, I used a small spreadsheet for working out what goes into which box in the VAT return. You can download it here and practise applying the different types of function_num argument with manual row hiding, and then with filtering. Suggestions: subtotal the Amount column on either UK (column B) or Net (column A). Just have a play – I’m sure you’ll find it’s a really easy function to use once you’ve tried it a couple of times!

If you’re following the suggested Learning Plan, please click here to go to the next post.

Getting to grips with Functions

Functions are absolutely the bedrock of Getting Things Done in Excel. They are so, so useful! Here, we’ll have a look at a few of the commonest functions – ones which you probably already know – just to lay the ground for a deeper understanding in following posts.

Firstly, what is the difference between a formula and a function? Well, functions all start with = so in fact they’re all formulae. Functions are a large collection of built-in formulae in Excel, all pre-programmed to do something specific. You can use them on their own or in conjunction, or even inside each other, to make a huge range of tools available. You can’t use a function designed for one thing, to do something else. Chances are, if you need your function to work in a different way, you need a different function.

Let’s have a look at SUM and branch out from there.

In the above gif, I make reference to typing all my functions. Sure, it’s what the pro users do, but that’s not the only reason. There are certain instances where you can’t insert a function without typing. These are covered briefly in the next post.

Further functions

In the next gif, you can learn a bit more about some other basic but extremely useful functions.

Even more you can do with functions

So far, we’ve looked at having a range of cells as the input in our functions, such as A1:A3. In the next gif, you will learn how you don’t have to stick with just a range, but can have a whole mix. Here are some examples to look at before you watch the gif.

Numbers alone	=AVERAGE (33, 44, 55)
Cell references alone	=AVERAGE (A1, A2, A3)
Ranges alone	=AVERAGE (A1:A3) =AVERAGE (A1:A3, B10:B15)
Numbers with ranges, ranges with cell references, cell references with numbers	=AVERAGE (55, A1:A3) =AVERAGE (A1:A3, B10) =AVERAGE (B10, 55)
Numbers AND ranges AND cell references	=AVERAGE (55, A1:A3, B10)

Have a go!

I have a few files in which you can have a go at practising your basic formulae and functions (and yes, I include the answers!).

This link is for a folder on my Google Drive which consists of a pdf with the Exercises in, three spreadsheets you’ll need to use, and three answer spreadsheets (no peeking!). It’ll download as a zip file, so once it’s downloaded, right-click on it and choose Unzip to be able to access them all.

Good luck – and remember, please contact me if you’re feeling at all befuddled!

If you’re following the suggested Learning Plan, please click here to go to the next post.

Nesting IFs

I always think that ‘nesting’ functions sounds kind of like birds getting all comfy for the winter, but disappointingly it’s much more prosaic than that. ‘Nesting’ is essentially putting one function in its entirety into another function – which we saw in action in the last post. (In fact, if you haven’t read it yet, it might help if you do.)

Big important note which you must read!

I mentioned all the way back here that it was pretty important to type your formulae out, rather than using the function builders which you get when you select a function from the Formulas tab. What you’re about to learn here cannot be done via those function builders. You just can’t. THIS is why I was so adamant that you should stop using them, if you’re in the habit, and learn to write them out. I even checked on my advanced Excel users group and they agree, there’s no way you can do it unless you type it out. Sorry!

Why nest the IF?

You may remember me saying that an IF statement is an either/or situation. We have a logical test, or statement, then we have what to do if that logical test is true and what to do if it’s false. That’s great if you only have two things.

But what if you have more than two? Life is rarely simple enough that we just have a this-or-that situation going on. We need to have shades of grey between the black and the white – and that’s when a Nested If would step into the breach: you put an IF inside another IF.

The example I’m going to use here is the grading system AAT introduced with AQ2016. Before, you either passed or failed – which was perfect for an IF; but now you can have pass, merit, distinction or fail – which is perfect for a Nested If.

Get organised

The first thing you have to do with a Nested If is to organise your shades of grey. You have to think carefully in top-to-bottom or bottom-to-top order. Thinking of our results, anything 90 or above is a distinction, 80 to 89 a merit, 70 to 79 a pass and below 70 is a fail. We’re going to set it up so that our score tries itself for size in the first level. If it doesn’t fit, it’ll try the next level; and so on, until it finds the perfect match.

On that basis, if we had our different levels all jumbled up, our score would have a devil’s own job trying to find which one it fits into, so it’s essential we get organised.

I was taught top-to-bottom (larger numbers to smaller numbers), so that’s the way I’ll show you here. Others are taught the other way up. Remember I said in the IF lesson that whatever you write it’s essential to look at your answer and see if it’s what you’re expecting – if you’re testing something is bigger than 5 and it turns out to be working on numbers smaller than 5 you know you’ve messed up somewhere! Same thing here. Test your function by trying various inputs and see if the output is correct. So if you go bottom-up rather than top-down, it doesn’t matter – just test it to check!

Laying out your function

Let’s think about the syntax for a minute. We’d normally have 3 arguments:
=IF ( logical_test, value_if_true, value_if_false )

Using our AAT exam results example, that would mean something like: IF the score in cell A1 is greater than or equal to 90, say “Distinction”, otherwise say “Merit”. But that suggests that everything below 90 is a Merit, which isn’t the case. So the problem here lies with our final argument, our value_if_false, because we could have more than one. It’s at this point we’ll nest an If.

So what we actually need to say is, IF the score in A1 is greater than or equal to 90, say “Distinction”. IF NOT, then IF the score in A1 is greater than or equal to 80, say “Merit”, otherwise say “Pass”. But again, that suggests that everything below 80 is a pass, so we’d need yet another Nested If!

So our final version is: IF the score in A1 is greater than or equal to 90, say “Distinction”. IF NOT, then IF the score in A1 is greater than or equal to 80, say “Merit”. IF NOT, then IF the score in A1 is greater than equal to 70, say “Pass”, otherwise say “Fail”.

You can visualise the decision-making process like this:

‘Translating’ into Excel-speak

Looking at the above, we need now to write it out in actual Excel-speak, so we can use it for real. Personally, I find it easier to break down a Nested If by writing each different level as a separate line – visually, it just makes more sense. You may find your own trick to keeping each level distinct – whatever works for you. 🙂

=IF ( A1 >= 90, “Distinction”,

IF ( A1 >= 80, “Merit”,

IF ( A1 >= 70, “Pass”,

“Fail” ) ) )

Written out in the Formula Bar it’ll look like this:

=IF(A1>=90,"Distinction",IF(A1>=80,"Merit",IF(A1>=70,"Pass","Fail")))

It looks a bit crazy, but as you type it, your guiding panel will help you. Just remember to move straight onto the next IF rather than the value_if_false, until your very last level. (Note: If the animated gif doesn’t show so well, download a PowerPoint file of it here.)

Important notes

• You only need one = at the start. Don’t keep putting more in, you’ll upset Excel.

• Each bracket you open needs a close bracket – which all go right at the very end. Watch carefully as you pair them up, as they’ll briefly go bold as they pair. I look to the very first open bracket and when I see that’s gone bold I know I’ve done enough. They also colour-code as well which is handy.

• For however many levels you have, subtract 1. That is the number of IFs you’ll need. For instance, here we have 4 levels therefore that’s 3 IFs. What I see lots of people do is get to the final level, and (continuing from above), say if A1 is less than 70, say “Fail”, but then … otherwise, what? You only need one final value_if_false, right at the very end.

When it all goes wrong

• The main reasons nested IFs fail is because of a missing comma. You have to be so careful to separate out each of your arguments with commas and they’re not that big on the screen so it can be so hard to see what you’ve done. When you confirm, you’ll get an error message, but Excel will often shade over the part it detects a problem with. In this example here I removed the second comma, thus ‘blurring’ the first value_if_true with the start of the second IF. But Excel has indicated the exact area of the issue.

• Another common error is missing brackets. Excel calls these parentheses, so if you get the following error, check your brackets are a) in the right place and b) paired, one opening for one closing in every case.

• It’s a classic mistake, with all your worry about brackets and commas, to completely forget to put your text in double quotes. If that happens, you’ll get the #NAME? error.
• Still getting stuck? Move right to the very start of your entire formula and type ‘ (a single quotation mark). This turns everything in the cell into text, and has the advantage that you can finally park it and walk away. There have been many times where I’ve been flummoxed by a problem and each time I’ve confirmed it’s thrown up another error, thus trapping me into a cycle of fix-error-fix-error. Before you get to the point of throwing your computer out of the window, STOP! Pop a single quote in, save your spreadsheet, take a deep breath and do something completely different. Your brain will thank you for it!

An alternative to a nested IF

You can nest up to 64 IFs – although the idea of doing this hurts my head! Oh my goodness, the brackets at the end! You have to admit, doing more than about 5 or 6 is a bit bonkers. So what could you do if you have more than a reasonable amount of options you need to return? Quite simply, use a VLOOKUP. A lot of people will just go straight for the lookup and not even bother with a nested IF.

However, nested IFs are useful for two reasons: firstly with a VLOOKUP you have to have a table somewhere with your lookup values in. Supposing a user finds it, and deletes it? If you have everything ‘hard-coded’ in a nested IF, that’s less likely to happen – and if you just have the single function rather than a separate table elsewhere, it’s easier to protect and lock down.

Secondly, and more critically, you can’t test for multiple conditions in a VLOOKUP. Supposing you need to check if you got more than 70 in two subjects in order to pass? (If you need to do this, see this post.) A VLOOKUP can’t look up more than a single value.

But I think ultimately it comes down to preference. Some people I know use lookups everywhere; me I’m more of an IF person!

Over to you

Download some exercises here (three worksheets in the one workbook). Bear in mind there are several different ways you can complete the exercises – I’ve given some variations on the theme in the answers but you might have come up with another version again – this is absolutely fine, you get Smug Points for a formula that works, not a formula that’s a carbon copy of mine!

The following post expands on the principle of nesting and gives some great additional directions in which you can take your spreadsheets. I’d very much recommend them to anyone who is already pretty clued up on Excel. However, what’s covered is not part of the AAT synoptic, so if you are pressed for time you can skip it.

To learn more about nesting, click here.
To continue with the suggested learning plan, click here.

The COUNTIF and SUMIF functions

I’d like to start our journey into more advanced functions with two very handy examples – they’re siblings, in fact, as they act very similarly.

Note that I’ll be using quite a few terms which you’ll find useful to learn, so if you haven’t yet had a read of this post, you may find it beneficial.

We’ll start with the little brother.

COUNTIF

As the name suggests, this function counts things – but only if they meet a particular criterion. The syntax is really rather simple:

=COUNTIF ( range, criteria )

(My inner grammarian is twitching like mad here: one criterion, many criteria… you can try asking it for actual criteria but it won’t work!)

So what’s it asking for? Well, we know a range is a group of cells. So we’re telling it to go to a particular range of cells. Once it’s gone there, it’s then going to look for what you tell it to look for.

As this image shows us, our answer would be 4, as there are 4 cells which say apples in B6:B15.

If we typed bananas into cell B2, our answer would be 2.

SUMIF

The big sister to the function above. What we have here is a function that adds things – but only if they meet a particular criterion. The syntax is almost exactly the same as COUNTIF, but it has one extra argument:

=SUMIF ( range, criteria, [sum_range] )

So again, the first argument is telling us we need to put in a range. The second argument is telling us we need to put in something for it to look for, in that range. The third argument is optional (remember, [ ] around an argument indicates that it’s optional) – it’s telling us we need to put in a range where there are some numbers to add up.

The third argument MUST therefore have numbers in!

The answer here is 1.80+4.17+4.24+5.06 = 15.27.

If I’d put bananas into B2, it would be 2.38+2.16 = 4.54.

In the example above, I could have chosen to add up all the net costs, in which case my third argument would be C6:C15. Or I could have added all the VATs, in which case my third argument would be D6:D15.

Can you see how these two functions are both related?

Variations on a theme

1 – the second argument, criteria.

There are two ways of inputting this argument. We can either have an actual value, or we can have a value in a cell ‘feeding into’ the function.

• If it’s the former, it would be =COUNTIF( B6:B15, “apples” )
• If it’s the latter, it would be =COUNTIF( B6:B15, B2 )

What are the advantages and disadvantages? What’s best practice? Well, as I stated here and here, if we have a value in the function itself, we have a big problem if we ever want to change that – from apples to strawberries, or to bananas. We actually have to edit the function – what a pain in the neck!

We also have to consider that if we’re putting text into this argument, Excel will think it’s the name of a function or named range unless we remember to put it into “double quotes”.  (Can you tell I’ve done this before?!) Do note that we have to use double quotes – two single ‘ (apostrophes) won’t work.

If we’re asking our users to know to do this, they’re going to be really frustrated when it doesn’t work. So always use the latter – have a cell ‘feed in’ to the function.

2 – The third argument, Sum_range

I mentioned that it’s an optional argument. Why is this? Surely it’s kind of the entire raison d’être of the function that we’re needing it to add? Well, it could be that the first argument contains all the numbers, in which case, seeing as it’s looking in there already, it doesn’t need to look elsewhere.

Obviously, if we’re looking in a range of cells containing nothing but numbers, then the only real criterion we can have is “look for something bigger than x” or “look for something smaller than y”. We can’t tell it to look in a bunch of numbers for a word, or we’ll get an error!

If we need to ask it to do this, look no further for how….

3 – Looking for things above or below a certain value

It’s obviously going to be that second argument, criteria, which does this job. We’re going to need to use these symbols to help:

• > greater than
• >= greater than or equal to
• < less than
• <= less than or equal to.

Tip: if you have trouble remembering which way round the arrow goes, write two small numbers, let’s say 5 and 3. The pointy bit always points to the smaller number: 5 > 3. You can then read it as five is bigger than three which tells you that the > is the greater than symbol! If you’ve written 3 and 5, then it’ll point the other way: 3 < 5. You can then read it as three is smaller than five which tells you that this way round it’s called less than.

So let’s say we’re looking in cells C6:C15 for anything bigger than 4.50. Here’s what we’d put – with examples of each of our functions:

• =COUNTIF ( C6:C15 , “>4.5” ) (count up anything in C6:C15 which is larger than 4.5)
• =SUMIF ( C6:C15 , “>4.5” ) (add up anything in C6:C15 which is larger than 4.5 – note that we don’t need that third argument in this case).

Have you spotted that we need to put our >4.5 in “double quotes”? I’m not entirely sure why this is, but if you find out, do let me know.

Your turn

I have the spreadsheet above, with the apples, strawberries and bananas, ready for you to have a practice with. Once you’ve done that, try out the exercises in the other two worksheets. Remember, take your time, look at what the arguments are asking for, be patient with yourself, and don’t forget your commas!

Download the exercises – and, as always, the answers – from here.

If you’re following the suggested Learning Plan, please click here to go to the next post.

More about Functions

Having looked at a few basic functions in the last post, and learnt a little bit more about how we can extend them from the basic single range input, we’ll now start to have a look under the bonnet of this valuable set of Excel tools.

The layout of functions – syntax

As I explained in the previous post, a function is pre-programmed to do a very specific task. Estimates vary, but there’s more than 400 of them – and if there’s something you need to do that doesn’t seem to have a function already, you can usually find an add-on or even write your own! They come in categories, such as Statistical, Engineering or Financial, which can help you if you’re browsing to find something that does what you want – but you absolutely don’t have to learn them all to be ‘good at’ Excel.

Now – functions all have a very specific layout to them. Once you grasp that, you can get the gist of loads of them. Let me explain.

Take normal old SUM. It’s =SUM(A1:A10)
Average is similar: =AVERAGE(A1:A10)
So is Max: =MAX(A1:A10)
And Min, and Count, and CountA…

They all have this same pattern:

1. equals sign – they’re all formulae
2. a name. You have to get this right, otherwise Excel won’t understand what you’re after
3. an open-bracket
4. bits between the brackets – these are called arguments and give Excel more detail about what you want to do with the function. You can have one argument, two arguments, sometimes even up to 255! They are always separated by commas. Some functions actually work without any arguments at all!
5. and a close-bracket.

The entire caboodle is known as syntax. You have to get the whole syntax correct for Excel to play nicely. If you don’t, however, you can often get clues as to what to do to fix it.

Now: the bits between the brackets – arguments. Most people think that SUM only has one argument, a range (e.g. A1:A10 – a group of cells, in other words). In fact, you can have loads, as we saw in the previous post. You could say (A1,A6) or (4,8,10) or even a mix (A1,5,B2:G15). Whatever you have, all SUM can ever do is add. That’s what it’s for.

It may sound silly to say this, because everyone knows SUM, right? But I still see =SUM(D1-B1). Why not just have =D1-B1? Or in other words, why are you telling an adding function to subtract?

So understanding that each function is designed for one thing and one thing only is actually really important.

Now to dig into this a little bit more, what I’d like you to do is to open Excel and press F1. This opens your Help files. Type in SUM function and it should appear. Below a brief bit of information about what it does, look for where it says syntax. See if you can understand the explanation. You can see it refers to arguments – remember, the bits between the brackets: the first argument is required, but the next – and subsequent 253 – are optional. So – 255 different numbers/ranges/cell references in total – wow! Suddenly our humble SUM is looking quite sophisticated!

More key terminology

At this point it’s really important to understand another key word: value. It’s a funny word that pops up often. What it is is actually any one of several things:

• text
• number (called ‘numeric’)
• text and numbers together (called ‘alphanumeric’)
• the answer to a formula or function.

I’d like you to focus on the last one for a moment: the answer to a formula or function. Think about a cell with a formula or function in it. Where do you see the actual formula? In the long white bar above your cells – the Formula Bar. And where’s the answer? Well duh, you’re thinking, it’s in the cell! But then, surely the formula is in the cell? But NO: here’s a critical distinction: you SEE the formula elsewhere – what you actually SEE in the CELL is the value.

A value is “what you see in the cell”. This is going to be particularly relevant when we look at IF statements and VLOOKUPs.

How Excel helps you when you’re writing a function

Now, think about when you start typing a function. As soon as you finish typing the name (SUM, COUNTIF, VLOOKUP, etc) and type that open bracket, you get a little panel appear under what you’re typing.

This is a guide to the syntax, and tells you what arguments you need. The argument which is bold is the one you’re currently writing. Anything in [ ] is an optional argument.

A big tip

I appreciate that so far I’ve been telling you to type or write your functions, and I’m fully aware there’s a nifty little tool which will help you build your function from any of the Function drop-downs on the Formulas ribbon. I really don’t advocate using this. (If you’re wondering what I’m talking about here, I’m actually not going to tell you because it will lead you up the garden path.)

Firstly, I’ve learnt from experience that students/delegates rarely get a proper sense of what they’re doing if they’re just filling in boxes. By actually typing and thinking hard about what you do, you learn much more effectively.

Secondly, with more complex functions (such as nesting, which you can read about here), you just can’t do this any other way – you have to type.

Learning a little bit more

Now, what I’d like you to do is slowly and patiently go through the Help files searching for each of the following in turn. Type NAME function in each case (for instance, SUM function or MAX function) to find them. Read the description. Look at the syntax. What clues are there in the arguments, words like ‘range’ and ‘value’, that can give you pointers?

Try these for size:

• PROPER
• CONCATENATE
• SQRT
• PRODUCT
• INT
• ROMAN

You’ll probably find you can understand every single one!

Go on, give it a try! Report back and let me know 🙂

If you’re following the suggested Learning Plan, please click here to go to the next post.