+ Reply to Thread
Results 1 to 8 of 8

When you limit the y-axis Max value, is there a nice way to demonstrate when a data spike

  1. #1
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    When you limit the y-axis Max value, is there a nice way to demonstrate when a data spike

    goes above that maximum value? Something cool and automated. Thanks,

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    More context, please. Are you looking for something in a chart (what kind of chart?), or something for a table?

    Can you post an example spreadsheet?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    help.xlsx

    Hope that clarifies. You see the c goes to 10, but I dont know how to make that clear in a nice way on the chart that I have capped at 3.

    Thanks so much

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    Here's one way:
    Add two helper columns.
    first: MIN(data, cap_value)
    second: IF(data > cap_value, cap_value, error)

    Then you can use the first+second helpers to build your chart; I stuck the second as a line chart so that the data marker for the line chart can be your "above cap" indicator, while the error code gets ignored by the column.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    Thanks Ben, shame there's no in built more elegant function

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    Quote Originally Posted by davidx View Post
    Thanks Ben, shame there's no in built more elegant function
    I dunno, this seemed pretty straight-forward and simple to me.

    I mean, you're already scraping up against bad practice, so it's probably a good thing that there's no intuitive-and-easy way to create deceptive charts, right?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    If you go here http://peltiertech.com/broken-y-axis-in-excel-chart/ down near the end of the tutorial right before he shows the finished panel chart, he explains how to apply a gradient fill effect to a data point to give the illusion that a specific column "extends into the clouds". Would that kind of effect work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Contributor
    Join Date
    11-13-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: When you limit the y-axis Max value, is there a nice way to demonstrate when a data sp

    Wow MrShorty thank you that's just what I needed. Brilliant!

    Ben Hensel...have you considered anomalous data ruining a graph by making it impossible to see the underlying trend? Best practice? For GCSE Biology coursework maybe! but thanks for your help anyway!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to de-spike the values on this graph?
    By cat3appr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2014, 10:58 AM
  2. [SOLVED] Loop to demonstrate all System BackColors?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2014, 06:33 PM
  3. Chart to demonstrate
    By seanyeap in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-05-2008, 10:21 AM
  4. Limit range on Y axis?
    By wplate in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-11-2007, 05:28 AM
  5. Limit value in Y axis
    By mukesh_thiru in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2005, 06:05 AM

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