How to use REFERENCE OPERATORS in EXCEL ?

INTRODUCTION

IF YOU HAVEN’T VISITED YET.

ARITHMETIC OPERATORS WERE ALREADY DISCUSSED IN THE POST INTRODUCTION TO EXCEL FORMULAS:ARITHMETIC OPERATORS

Similarly, reference operators as important as the others and very important for the basics of the EXCEL.

Reference Operators specifically provide us the option to refer to the ranges in different ways.

In this article , we’ll focus on the reference operators which are frequently used in Excel.

RANGE OPERATOR is used by the sign COLON “:”.

Range operator gives the reference to all the address which come between the two operators.

e.g. if we use as A1:A15

it takes all the cell between A1 and A15 including A1 AND A15.

e.g. If we want to sum up all the cells from A1 to A15 we’ll type this function.

=Sum(A1,A2….A15) OR with the help of Range operator we can write

=Sum(A1:15)

THIS EXPRESSION WILL SUM ALL THE NUMBERS IN THE CELLS A1 TO A15.

*The exact example shown at the end of article.

UNION OPERATOR is used by the sign COMMA “,”.

UNION OPERATOR combines the references of two ranges into one reference.

For example

Suppose there are two ranges A1:A5 and other range from B1:B5 and we want to sum up all the cells the formula will be like this one.

=SUM(A1:A5,B1:B5)

THIS EXPRESSION WILL TOTAL ALL THE NUMBERS GIVEN IN THE CELLS A1 TO A5 AND B1 TO B5.

*SUM is a function meant for summing up the contents.

*The exact example shown at the end of article.

INTERSECTION OPERATOR is used by the sign SPACE ” “.

INTERSECTION OPERATOR (AS MEANT BY INTERSECTION IN THE SET THEORY) refers to the cells common in the two ranges.

For example, Suppose there are two ranges A1:A5 and A3:A7

it’ll be used as =SUM(A1:A5 A3:A7)

This expression will result in the SUM OF THE CELLS WHICH ARE COMMON IN BOTH THE RANGES i.e. A3,A4,A5

*The exact example shown at the end of article.