+ Reply to Thread
Results 1 to 5 of 5

Automate Scatter Plot with data points being numbers

  1. #1
    Registered User
    Join Date
    02-10-2009
    Location
    Shakopee, MN
    MS-Off Ver
    Excel 2003
    Posts
    14

    Automate Scatter Plot with data points being numbers

    Hope someone can help me out. I have a Contribution Analysis of the food items in our Innovations dining section. Currently, I manually plot each of the 'codes' onto the attached chart. The reason for doing this manually is that the number that is plotted has a specific reference to the food item.

    So, if we look at the attached, very first item is 'Farfalle DeSalvo Pollo' - with a Y-axis of 14 and a X-axis of 11. So, within those cordinates, a '1' will appear for the food item 'Farfalle DeSalvo Pollo'.

    Is there anyway to automate this process? I do this process for 6 other graphs - same concept and it is very time consuming.

    Thanks in advance for any advice.
    Attached Files Attached Files
    Last edited by Grateful; 02-24-2009 at 12:27 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Automate Scatter Plot with data points being numbers

    In C5, this array formula: =IF(SUM( (C$27=$AF$5:$AF$25) * ($B5 = $AE$5:$AE$25)) = 0, "", MATCH(TRUE, (C$27=$AF$5:$AF$25) * ($B5 = $AE$5:$AE$25) > 0, 0))

    Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

    The copy across and down.
    Last edited by shg; 02-23-2009 at 08:57 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-10-2009
    Location
    Shakopee, MN
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Automate Scatter Plot with data points being numbers

    Thanks for the information! This works perfectly.

    Quick learning question regarding the formula... what does the TRUE mean in the MATCH formula? Also, what do the asterisks stand for?

    I'm new to some of these formulas... thanks for your patience!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Automate Scatter Plot with data points being numbers

    what does the TRUE mean in the MATCH formula?
    The middle part of the formula,

    MATCH(TRUE, (C$27=$AF$5:$AF$25) * ($B5 = $AE$5:$AE$25) > 0, 0)

    ... returns a series of logical values. The function locates the first one that is True.

    Also, what do the asterisks stand for?
    The asterisks are the multiplication operator. When you force a logical value to become part of an arithmetic operation, True and False are coerced to 1 and 0 respectively.

  5. #5
    Registered User
    Join Date
    02-10-2009
    Location
    Shakopee, MN
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Automate Scatter Plot with data points being numbers

    Perfect. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1