Operators in Excel: in casu :
Posted by Theo Heselmans on August 23rd, 2007
You all know that +, -, * and / are operators, and you use them extensively in Excel. There is one operator you also use regularly, but you might not be aware that it's also an operator. I'm talking about the : (colon).
A colon 'joins' 2 references; e.g. SUM(A2:A13) will summarize all cells starting from A2 up to and including A13. You knew that.
When you start playing with the references before and after the colon, the fun begins.
Let's say you need to have a running total. Here's a screenshot.
Notice the formula for the running total in C6: SUM($B$2:B6). This formula is exactly the same (relatively speaking) in all cells from C2 up to C13.
$B$2 is always the same fixed cell, B6 'varies' according to the cell.
I told you before that the INDEX function (as well as the OFFSET function) results in a reference. This means you can use them in combination with the colon-operator too.
When working with Names, they become even more useful.
Suppose you define a name for the 12 month revenue range,
and call B2:B13 'Revenue'.
Now you can e.g. calculate a quarterly total using INDEX:
The formula for the Q-total is easy (it looks complex, but only because I calculate the beginning and ending month based on the quarter):
- =SUM(INDEX(Revenue,(Quarter-1)*3+1):INDEX(Revenue,(Quarter-1)*3+3))
Category: Microsoft Excel | 
Technorati: Microsoft, Excel
Comments (2)
Regarding colon and excel - here's one for you.
Enter the following into an empty cell:
1:58:435 - do not put an '=' sign before it, or anything else. Do not put any special formatting on the cell.
then try
1:100
I cannot figure out what Excel is doing with this - can you?
@tman,
There are 2 ways Excel handles a colon. One is used as an operator, as explained in my post, the other one is for formatting time.
In you 1st example 1:58:435 is treated as 1 hour, 58 minutes an 435 seconds. If you know that 24:00 = 1 and 12:00 (noon) = 0.5, then you an calculate your number too.
1:100 equals 1 hour, 100 minutes equals 2:40. Format this number in Excel as time, and you will get 2:40.
Hope this helps.