# Thread: spaces in formula (Excel 2003 (SP2))

1. ## spaces in formula (Excel 2003 (SP2))

I have a workbook from a co-worker and he has several formulas of a style I've never seen (space between names).

=nameAA nameCC

These refer to two ranges of different sizes. NameAA is 40+ cells (all in one row) and nameCC is 3 cells where one of the 3 overlap the address for nameAA. Somehow Excel is fine with this and generates an answer but what mathematical function is actually being called? Is it short hand for something?

// Deb <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

2. ## Re: spaces in formula (Excel 2003 (SP2))

The "space operator" (aka the "interception operator") produces a reference to the cells common to 2 ranges (that is the intersection of the 2 ranges)

Steve

3. ## Re: spaces in formula (Excel 2003 (SP2))

Thank you for that Steve. It's new to me.

4. ## Re: spaces in formula (Excel 2003 (SP2))

Well that certainly is something I've never heard and I'm pretty good with Excel and VBA. So it means to take the value at the intersection of these two ranges (and assuming only one cell intersects - only one cell is common). It still seems strange since the person writing the formula can just look and see what the intersection is, it's not calculated so why not just refer to that one cell?

I looked up 'intersection' in a John Wallenback book I had and sure enough, he shows it like =SUM(B5:B14 A16:F16) which looks like it's saying add the number(s) that intersects from these two ranges. It should be the same without the SUM formula too which is what I have in my workbook.

Mystery solved but I'm struggling with trying to figure out a real use for this In futher reading I see that it can return multiple values and those values are operated on by whatever the function is so in the example above, if the intersection is 4 cells then their values will be added together. Walkenbach's book calls the use of the space with named range references (as in my workbook) as an 'explicit intersection.' He also mentions an 'implicit intersection' where the value returned changes based on where the formula is placed.

Thanks for figuring this out for me.

Deb

5. ## Re: spaces in formula (Excel 2003 (SP2))

I don't see much use for intersection formulas involving literal range addresses, for example =A4:F4 C1:C16
But if you have created defined names, e.g. rows named January, February etc. and columns named Sales, Cost, Profit etc., you could use =April Sales which is a nicely self-explaining formula.

6. ## Re: spaces in formula (Excel 2003 (SP2))

This is the type of scheme I have always seen it in, to do a shorthand, more intuitive reading of a value from a table instead of using index with a match on the leftmost column and the top row.

And I know you are aware of these things, but to add some additional info for others:
While the formula is more "self-explaining" setting it up requires more work as you must create a name for each row and for each column so there is an initial setup and also a maintenance to be done when new columns are rows are added. Neither of these are required with the INDEX/match combos.

You also must be careful with the named ranges if the data is sorted later as the names most likely will not stay with the right listing.

Steve

7. ## Re: spaces in formula (Excel 2003 (SP2))

Good points. For the reasons that you mention I don't use this way of referencing a range myself, it's more work than it's worth to me. (I do use named ranges for other purposes)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•