Results 1 to 7 of 7
  1. #1
    5 Star Lounger jujuraf's Avatar
    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 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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Silver Lounger
    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

  4. #4
    5 Star Lounger jujuraf's Avatar
    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

  5. #5
    Plutonium Lounger
    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 self-explaining formula.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 "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. #7
    Plutonium Lounger
    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)

Posting Permissions

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