Brackets And Commas in Excel
I am regularly asked for help with Excel files where people don’t understand some of the formulas used. In some cases the person is just not familiar with the formulas but there are often times when the person understands the individual formulas but can’t understand what they see onscreen because several formulas are used together.
If you pay attention to the brackets and commas in the Excel formula bar you can break down what you see and hopefully start to understand what you are looking at.
Over the coming weeks I will talk about some of the formulas I’m usually asked to explain or demonstrate.
In all these cases, brackets and commas are important to follow – this post is a brief introduction to brackets and commas in Excel.
Two uses of brackets in formula:
- To contain formulas – for example an “IF” statement. The parts of the formula are contained between two brackets. To demonstrate this we will look at an example formula =IF(A1>2,0,2).It is not important at this stage to understand the formula for now, the important thing to see is that all the parts (condition, value if true, value if false) of the IF statement are contained within the brackets.
- To separate formulas where one formula is made up of several – If you wanted to add 15 and 27 together and multiply the answer by 5 you would expect to get 210, but the formula =15+27*5 would actually give you the answer 150. Why?? This is because the multiplication and division are given priority and then the 15 is added to the answer. If you entered the formula as =(15+27)*5 you would get the answer you expected. Here Excel treats the addition within the brackets as its own formula first and then uses that answer in the multiplication formula.
Some formulas are made up of several parts. For example, the IF statement above is made up of 3 parts
- The condition – what is your IF statement checking
- What to do if the condition is true
- What to do if the condition is false
How do we know which part of our formula above relates to any of the parts just listed? The answer is…. the commas tell you.
Each part (1,2 and 3) are separated by commas. You must enter a comma to end one section and enter the next section.
Taking our sample formula again I have commented in red font each section – note the commas separating each section of the formula
=IF(A1>2 Our IF statement will check if the value in A1 is greater than 2 ,0 IF our condition is true then return the value 0,2IF our condition is false then return the value 2)
Keep these two important punctuations in mind for the upcoming posts about formulas and your life with Excel will be made so much easier!!