Today I am going to share various Math operators in excel, more exciting stuff ahead so let's dive into the topic.
Types of Operators:
Ampersand ("&") - Joining Operator.
Space (" ") - Intersection Operator.
Comma (",") - Union Operator.
Colon (":") - Range Operator.
Joining Operator:
As given in below picture, you can see that ampersand is used to join two or more text strings.
Range C3 and Range D3 can be joined using the joining operator ampersand, and the final output is delivered in E3 cell.
We can join 2 or more range of cells using ampersand as shown below.
But each range has to have the same size, otherwise it will throw an error as shown below.
Year column has 12 rows and Month column as 11 rows, Because of the size difference it throws error in 1 cell.
Even though both ranges are of same size but starts or end with different rows can produce deformed result. So we have to be so sure in selecting the ranges when the cell is in point mode.
Intersection Operator:
In Excel CHAR(32) that is Space(" ") is used as intersection Operator.
In basic lookup functions like VLOOKUP or INDEX, we extract the intersection value or intersection range of Row and Column. Below is the simplest form of two way lookup using Index and match function which returns Apr month Col3 intersection at L5 cell.
This can be replaced by space as shown below. In formula bar, we can see that range D6:G6 which corresponds to Apr month and F3:F14 which Corresponds to Col3 is separated with space. These two ranges intersect at F6 and the formula exhibits 637 in N6 cell. Here both of the range are static since it is hard coded, but we can make it dynamic by naming the ranges and using the indirect function which we can see in further posts.
Comments