Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm writing up this handout, and I would appreciate another pair of eyes on it. If the information in it is useful to you - so much the better.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  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
    Here is a way with some intermediate data, to plot all as one chart (I despise trying to overlay charts): I combined the average and score into 1 stacked chart of 5 columns (you can change the number to adjust the width of the score)

    As and "enhancement": I also added a combo box to select a person and have the chart updated with that person and their score and added a datalabel to show the average and textbox to display the person and score on the chart....

    Steve
    Attached Files Attached Files

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='sdckapr' post='794102' date='19-Sep-2009 18:47']Here is a way with some intermediate data, to plot all as one chart (I despise trying to overlay charts): I combined the average and score into 1 stacked chart of 5 columns (you can change the number to adjust the width of the score)

    As and "enhancement": I also added a combo box to select a person and have the chart updated with that person and their score and added a datalabel to show the average and textbox to display the person and score on the chart....

    Steve[/quote]

    Hi Steve
    Pretty nifty - but the charts don't display properly if the students' score is below average.

    I think the intermediate data route is the way to go for a more sophisticated excel user. I'm thinking that for a more novice user the overlay charts method is easier.

    C
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    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
    [quote name='WebGenii' post='794449' date='22-Sep-2009 13:29']Hi Steve
    Pretty nifty - but the charts don't display properly if the students' score is below average.

    I think the intermediate data route is the way to go for a more sophisticated excel user. I'm thinking that for a more novice user the overlay charts method is easier.

    C[/quote]

    My mistake with the charts displaying incorrectly. I forgot to set the min of the secondary axis to zero. Once this is done, it works fine...

    Personally, I find the overlay more frustrating than the intermediate formulas and I think it will problematic for both novices and sophisticated users. The intermediate route uses essentially the same technique you are proposing, it just adds a level of added detail.

    I don't use these charts and I only offered the method as a means around what I saw as a "problem" with your methodology that I saw a solution to..

    Steve

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='sdckapr' post='794479' date='22-Sep-2009 17:02']My mistake with the charts displaying incorrectly. I forgot to set the min of the secondary axis to zero. Once this is done, it works fine...

    Personally, I find the overlay more frustrating than the intermediate formulas and I think it will problematic for both novices and sophisticated users. The intermediate route uses essentially the same technique you are proposing, it just adds a level of added detail.

    I don't use these charts and I only offered the method as a means around what I saw as a "problem" with your methodology that I saw a solution to..

    Steve[/quote]

    Hey, didn't mean to sound snarky in my previous post. I do appreciate another pair of eyes looking at things. The scientists I work with seem to have a strong anti-intermediate bias (and an anti-Excel charting bias, but that is another story). I reference Charles Kidd at the end of the handout (that's the benefit of writing for scientists, you can have references at the end - they love it). Maybe a summary of different techniques?

    In Matt Grams post he was mentioning charting multiple bullet graphs simultaneously. I think the intermediate technique would really need to be laid out effectively to work well in that situation, which then leads to a whole discussion of effective spreadsheet design. I was just writing a handout, not a novel! <grin>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  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
    The scientists I work with have no intermediate bias nor excel chart bias, but that is perhaps because, being a scientist I speak their language

    Matt Gram's approach requires less intermediate manipulation than mine, but seems to be limited. The target must be in a defined region (in his examples it was in the low region). It can be in any region but the region must always be the same. If the target can move regions based on the data, it will have to become more complex a setup.

    For me, all the data for a chart could be intermediate calcs based on a design not setup to chart. I have put in "filters", sorted data and done all kinds of manipulation with formulas in order to chart the way we wanted with a data entry scheme that was not setup to chart. It all depends on what you want and need to do.


    Steve

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='sdckapr' post='794637' date='23-Sep-2009 17:26']The scientists I work with have no intermediate bias nor excel chart bias, but that is perhaps because, being a scientist I speak their language [/quote]
    Hey - that's an unfair advantage!

    Well the real purpose of the handout is general education. Open people's eyes to the pros/cons of working with multiple axis. Introduce chart templates. I'm not actually solving a specific problem here.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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