Results 1 to 7 of 7

20080514, 04:18 #1
 Join Date
 Jun 2001
 Location
 San Jose, California, USA
 Posts
 1,061
 Thanks
 0
 Thanked 0 Times in 0 Posts
spaces in formula (Excel 2003 (SP2))
I have a workbook from a coworker 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>

20080514, 04:47 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20080514, 05:13 #3
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: spaces in formula (Excel 2003 (SP2))
Thank you for that Steve. It's new to me.
Regards
Don

20080514, 08:16 #4
 Join Date
 Jun 2001
 Location
 San Jose, California, USA
 Posts
 1,061
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20080514, 08:51 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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 selfexplaining formula.

20080514, 14:13 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 "selfexplaining" 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

20080514, 14:32 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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)