 Administrator
Creator of financialmodelling.net
Posts: 71
|
|
I recently saw a model which has spaces in the formulae - presumably in an attempt to make it easier to read. I was tut-tutting that this may not be innocent and risk free and discovered that not a lot of people know that a space in a formula is an operator, known as the INTERSECT operator.
If your formula contains two range references, separated by a space character, it will return a range representing the cells that are in both ranges - ie the overlapping area or INTERSECT of the two ranges.
So, if you have the number 1 in all cells from A1 to C10 and enter a formula in A12 as follows;
=SUM(A4:C6 B1:B10)
The value returned is 3
If you select the formula in A12 again and hit F2 to edit it, you will see the two ranges in your formula highlighted and notice that the range B4:B6 is in both - it is the overlapping area and contains three cells each with a number 1 in. The sum formula wrapped around the reference means the whole formula evaluates to 3.
We then experimented with this some more and found that any form of reference works (for example ranges that are returned by the OFFSET formula) and multiple ranges separated by multiple spaces will return a range representing only the cells that are in all the ranges in the formula.
So - a word of caution.......
I'm all for clarity and making formulae easier to read, but spaces in your formulae may not be entirely innocent.
Also, if you can think of a need for this kind of reference, you'll confuse the heck out of most people who look at your formula!
If you want to break up your formula, you can use Alt+Enter which puts a line break in the formula bar. As far as I am aware, this doesn't have any side effects (apart from possibly obscuring part of the spreadsheet as the formula bar grows downwards).
I hope this is helpful.
Happy modelling
Stephen |
|
Financialmodelling.net Discussions › General Boards › Excel/Access › Tip - Spaces in formulae
Author
Tip - Spaces in formulae (currently 113 views)
Logged
Locked Board