Understanding Excel Operators – Guide - KING OF EXCEL

Saturday, December 21, 2019

Understanding Excel Operators – Guide

“Operator! How can I help you?”
This is what you hear when you call an operator in a hotel or company etc. Operators are there to help. Their operation is to implement and execute your command.
Even a simpler example is to ask someone “what is 2+2?” With this question you are also giving away what to do with two 2s. You asked to ADD them. Hence the other person will perform the operation of adding and give you the result “4”
Same way Excel is there to help you. You give it a task and also tell it how to operate. For example to ask Excel to do the same you have to write a formula =2+2 and press Enter. Excel will return 4. Again it is the “+” sign that completed the command. Hence the name operator.

In Excel we have several operators that we must know especially while writing formulas. Following is the list of operators I know so far with their names, what they do and some examples for each:
Arithmetic operators
SymbolNameOperationExample
+PlusAdd numeric values7+6, H7+S6
-SubtractionSubtract numeric values7-6, H7-S6
*MultiplicationMultiply numeric values7*6, H7*S6
/DivisionDivide numeric values76/67, H7/S6
^Caret or ExponentExponentiate numeric value7^6, ' H7^S6
%PercentageDivide with 100 to get percentage value7/6%, H7/S6%
()ParenthesisPrecedence specifiers. Enclosed item is determined first8*(7+6), HF14/(H7-S6)
Reference operators
SymbolNameOperationExample
:RangeJoins two cell references to determine a range of cells enclosed by the specified cells including the two mentionedA1:B3
This range will enclose cell A1, A2, A3, B1, B2 and B3
,UnionCombines the cells or range of cellsSUM(A1:B2,C7:D8)
Finds the sum of values within ranges A1:B2 and C7:D8
spaceIntersactionDetermines the range that is common between two rangesA1:C7 B3:D5
It will render the range B3:C7 as an array. Meaning that you have to wrap the output in some formula
$AbsoluteMake the reference absolute when included and relative when excluded with respect to column and/or row$A1 makes column A static and 1 relevant meaning if fill handle is dragged down the row address will update from A1 to A2 and so on. However, if fill handle is dragged to right then A won't change to B or C as its made absolute or static
Structured reference operators
SymbolNameOperationExample
@Current row specifierUsed to specify current row in formulasSales[@Actual]
Refers to value in current row of Actual column of Sales table
[]Column specifierUsed to specify specific column in the tableSales[Budget]
Refers to budget column of the sales table
#Special item specifierUsed to specify specific area of the table#All: refers to the whole table including headers and total row below
#Data: refers to enclosed data of the table but exclude total row and header row
#Headers: refers to the header row of the table
#Totals: refers to the totals row of the table
'Escape characterUsed to relieve Special characters to be used as normal[@Sales]*[@['#Quantity]]
As the header is named as "#Quantity" and # is a special character and used for special operation, to relieve (') is used so that it can be used normally as name
:Column range specifierJust like cells range, here it is used to specify range of columnsSUM(Table1[[Sales]:[Revenue]])
Sum the values within all the columns starting from Sales till Revenue column
,CombinerUsed to combine special item specifier and column specifierJuly[[#All],[Quantity]:[Revenue]]
Refers the range of cells starting from Quantity column until Revenue column including header and total rows
Comparison operator
SymbolNameOperationExample
=EqualDetermines if two items are equal.6=7 will be FALSE
6+7=7+6 will be TRUE
<Less thanDetermines if an item is less than the specified one6<7 will be FALSE
6+7<7+6 will be TRUE
>Greater thanDetermines if an item is greater than the specified one6>7 will be FALSE
6+7>7+6 will be FALSE
>=Greater than or equal toDetermines if an item is greater than or equal to specified item6>7 will be FALSE
6+7>7+6 will be TRUE
<=Less than or equal toDetermines if an item is less than or equal to specified item6<7 will be TRUE
6+7<7+6 will be FALSE
<>Not equalDetermines if two items are unequal6<>7 will be TRUE
6+7<>7+6 will be FALSE
Wildcard operators
SymbolNameOperationExample
*String of charactersFinds matching results with any number of characters before, after or within specified stringIn the find box, search box of filter and some formulas h* will fetch all the results starting with letter h
?Single characterFinds matching results with only single character before, after or within specified stringh??s will fetch only such results that have values starting with h and ending with s and inbetween any two characters
~Escape characterUsed to disengage * and ? Operators from acting as wildcards~* will find all such values that have * character in it e.g. m**nsoon
Other operators
SymbolNameOperationExample
&ConcatenationCombines two strings to make one text string"The sales for this"&H7&" is"&A8&" dollars"

1 Arithmetic operators

1.1 Basic arithmetic operators: + – * /
Addition, Subtraction, Multiplication and Division are done in almost same fashion as we normally do in real life. The only difference is that instead of using X for multiply we use asterisk *.
Excel operator 11.2 Exponent, Percentage and Parenthesis
Exponent and percentage operators are again simple. The role of parenthesis is same as we learnt in 4th grade mathematics
excel operator 2

2 Reference operators

2.1 Range operator
In excel we use colon (:) to specify range. In the following figure range A3:B5 include cells A3, A4, A5 and B3, B4, B5. As you can see the range is starting from the cell mentioned on the left of colon and ending on the cell mentioned on the right of colon.
excel range specifier operator
To sum the values in range A3: B5 we will the formula: =SUM(A3:B5)
excel operator 3
2.2 Range union operator
To combine two ranges of data inside formula we use comma (,). For example if I have to combine range A3:B5 and C6:E7 then I will write it liks this: (A3:B5,C6:E7). Following figure shows the ranges shaded in gray:
excel union operator
To find the sum of unified ranges the formula will be: =SUM(A3:B5,C6:E7)
excel operator 4
2.3 Range intersection operator
Intersection operator determines the range that is common between two specified ranges and is denoted with a (space). For example the intersection of ranges A3:C6 and B3:D7 is B3:C6 as it is common in both ranges. As you can observe intersecting range starts from the first cell of subsequent range i.e. B3 and ends at the last cell of former mentioned range i.e. C6
Following figure shows the intersecting range enclosed in both blue and red boxes:
excel intersection operator
In order to sum value of intersecting range within A3:C6 and B3:D7 the formula will be: =SUM(A3:C6 B3:D7)
excel operator 5
2.4 Absolute reference operator
This operator works on cell references and comes handy when you use quick fill meaning when you drag the fill handle up, down, right or left. Remember cell reference is a combination of row and column address. For example cell B3 is in column B and row 3.
If cell B3 is referred in another cell and fill handle is dragged, Excel will automatically update the address with respect to direction of drag. Observe the following figure closely:
excel operator 6
In cell A9 I referred to cell B3 and then dragged the fill handle right. Jumping column C D E and so on. Now later when I highlighted the cells by moving the selector, you can see in the formula bar that reference has changed from B3 to C3, D3, E3 and so on. As F3 and later cells are empty that is why the result is 0.
Now have a look if I drag the fill handle down:
excel operator 7
I started again from cell A9 by punching B3 reference again. But this time dragged the fill handle down and trotted down the rows moving over row 10, 11 12 and so on. And if you see in the formula bar while I highlight each cell you will see the address has changed from B3 to B4, B5 and so on.
As I said earlier, if you references are relative Excel will update the address depending on the direction of fill drag.
And if I drag the fill handle to whole region then it will be like this:
excel operator 8
Now here you can observe that not only rows has been changed as we go down from cell A9 but also columns have changed as we moved to the right from cell A9.
The reason is BOTH rows and column references were relevant and will change if cell reference is dragged.
So what if we want to:
change only rows and keep columns static: for this we will change the formula as follows; $B3
excel operator 9
You can observe that columns remained the same in all the references however, the rows were updated from 3 to 4, 5 and so on.
Change only columns and keep rows static: for this we will change the formula to; B$3
excel operator 10
This time row was static but column was changing from B to C D and so on as evidenced by change of value
Make both rows and columns static: for this we will change the formula to; $B$3
excel operator 11
By doing so, you can observe that value stayed the same i.e. 2 which is the value of cell B3.

3 Structured reference operators

Excel tables are completely different specie when it comes to references. Excel tables can cater normal reference syntax but they have their own system as well called Structured reference system.
Structured reference system gives much more power to the user while writing formulas and enhances readability and understanding. To learn about Excel table and why they are my favourite check out: 20+ Excel Table tricks to turbo charge your data
Remember: Every table has its unique name and it can be referred to with its name in the formula instead of mentioning the range. Following is one example in which I have already named the table Operator. After equal sign if I punch “O” you can see the name of table as suggestion. This makes referring to range A3:D6 much easy as I don’t have to use cell reference method.
If I mention “Operator” it is enough to refer to table’s data, which you can see enclosed within blue border. Not the whole table though as it excludes header.
3.1 Column Specifier
With tables it is very easy to refer to particular column as a whole. Normally we have to give a starting cell and ending cell reference but with tables we have to give just the column header name. To do this we use column specifier which are square brackets.
excel operator 12
Once you mention the table and insert opening square bracket, Excel will suggest the columns and you can select from the list or type the whole name yourself followed by closing square bracket. In the following figure you can see the data of Revenue column selected by typing: =Operator[Revenue]
excel operator 13
3.2 Special Item specifier
Excel tables are like a separate entity that encompass different items in itself. And to refer to each item we use special item specifier. In one table we table we have:
Header row: is at the top of the table which can be turned on/off
Data: is the range where values of different column and rows exist
Total row: an optional row which can be turned on/off and helps in making quick calculations e.g. sum, subtotal etc.
The following figure shows each element labeled for your convenience:
excel-special-item-specifiers
In order to specify each item we have to use special item specifier which are as follows:
#Headers: This is used to specify only header row of the table and its contents. In our case its address is A3:D3 and in formula it is specified as =Operator[#Headers]
excel operator 14
#Totals: this specifier is used to mention only the total row of the table. In our example it is A7:D7 and is specified in the formula as: =Operator[#Totals]
excel operator 15
#Data: this specifier helps referring to the table’s data which is sandwiched between header row and total row. In our case its address is: A4:D6 and in formula is written as: =Operator[#Data]
excel operator 16
#All: So far we saw specifiers that referred to parts of table, what if we want to refer to whole table? In this we use #All specifier. As you can see in the following illustration #All specifier selects the whole table including header and totals row:
excel operator 17
3.3 Combiner
Now that you know both column and special item specifier you can mix them together to make even fancier selections. For example if you want to select the data of only revenue column then it will be done using: =Operator[[#Data],[Revenue]]
excel operator 18
So in order to combine special item specifier and column specifier you need to separate them with combiner which is coma (,)
3.4 Column range specifier
So far we were able to specify either whole rows or data or a row or data of specific column. What if we want to specify multiple columns? In this case we use column range specifier which happens to be the same as usual range operator i.e. colon (:). The only difference is that now we will have column names on the left and right of colon than the usual cell addresses.
Suppose we want to mention the data starting from quantity column until revenue column then it will be done using formula: =Operator[[Quantity]:[Revenue]]
excel operator 19
If we want to refer to header row cells only starting from quantity column until revenue then it will be done using: =Operator[[#Headers],[Quantity]:[Revenue]]
excel operator 20
3.5 Current row specifier
Until now we have made quite a progress in understanding not only operators used in structured references but also understand how structured reference system is different from usual system in use.
Lets say I want to calculate the revenue of sales by multiplying the values of quantity column and sales column. To get this done I want to multiple the value in each row of quantity with the respective row value in sales column. To do this we will use this formula in revenue column: =[@Sales]*[@Quantity]
The @ symbol makes sure that each row item is considered instead of whole column.
excel operator 21
3.6 Escape character
In the above examples we learnt that @ # or , characters have special use and Excel considers them as operator. What if our headers have any of such characters? In such situations naming the column can be difficult.
For example if one column is named as @Quantity. If we refer to such column with @ then Excel will treat it as row specifier whereas we want to use it as part of name. To get around it you simply have you put apostrophe before such characters and Excel will not consider them as specifier.
Have a look at the following illustration:
As one column is named as @Quantity then our revenue calculation formula will be like this: =[@Sales]*[@[‘@Quantity]]
excel operator 22

4 Comparison Operators

Like basic arithmetic operators, comparison operators work the same way as we use them in our daily lives. They help us compare the data and give us result either in TRUE or FALSE.
excel operator 23

5 Concatenation operator

Often we have our data scattered in different cells. In order to combine the data to show as one textual string, we can use CONCATENATE function or its short form (&) to join the data from different cells.
In the following illustration we have data in three cells and we also want to add few words from our own. We will do this using concatenation operator
excel operator 24

6 Wildcard operators

These operators help in searching something and can be used either in Find & Replace feature, Filters or select formulas of Excel.
Asterisk: if used returns a matching results before, after or within string. For example you have PakAccountants, PakArtlive, PakProfessionals etc
If you put Pak* in the search box and run find it will select all three because all three start with Pak and asterisk will get all such results that start with Pak. But if you make it PakA* then it will select only first two as the last one does not match after “PakA” string.
excel operator 25
If asterisk is enclosed within two strings then it will fetch all such results that contain both strings. For example if we search p*r then it will fetch both PakArtlive and PakProfessionals:
excel operator 27
Question: If used is considered as a single character. Suppose you have; Pak, PakAcc, PakAccountants.
In the search box if you mention Pa? then it will select all the records that have ATLEAST one character after “Pa”. Similarly if you put Pak? then find will result only in selecting PakAcc and PakAccountants.
excel operator 26
If you enclose it within two strings then again it will work as single character. If you put P?k then it will find all the records that have P and also have one character between P and K as following example shows in which Paak wasn’t selected:
excel operator 28
Tilde: This is an escape character that forces * and ? to be considered as normal characters instead of wildcards in searches and formulas. I have written a detailed tutorial on this and please consider reading it: “Call Tilde” to tame Excel Wildcards – Find and Replace Wildcard Characters [Quick Tip]
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment