+ Reply to Thread
Results 1 to 6 of 6

chart to represent the value (below and above target )

  1. #1
    Registered User
    Join Date
    08-22-2020
    Location
    Dubai
    MS-Off Ver
    365
    Posts
    66

    chart to represent the value (below and above target )

    Hi Experts

    what is the best chart to represent the value to show it below and above target ?
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: chart to represent the value (below and above target )

    May be as 2 separate series for below and above target? And as above target are significantly shorten than below - different scales? See attached. Below graph are calculations of values used to setup limits and units for axes.
    Attached Images Attached Images
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-22-2020
    Location
    Dubai
    MS-Off Ver
    365
    Posts
    66

    Re: chart to represent the value (below and above target )

    Quote Originally Posted by Kaper View Post
    May be as 2 separate series for below and above target? And as above target are significantly shorten than below - different scales? See attached. Below graph are calculations of values used to setup limits and units for axes.
    Many thanks

  4. #4
    Registered User
    Join Date
    08-22-2020
    Location
    Dubai
    MS-Off Ver
    365
    Posts
    66

    Re: chart to represent the value (below and above target )

    Quote Originally Posted by Kaper View Post
    May be as 2 separate series for below and above target? And as above target are significantly shorten than below - different scales? See attached. Below graph are calculations of values used to setup limits and units for axes.

    Hi Kaper

    I want more clarification to understand how I can create such chart by my self . Could you write the steps for your chart I tried by myself not come like your chart

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: chart to represent the value (below and above target )

    The first part is quite obvious. As the time in excel cannot be negative, the formulas in C2 and D2 are respectively:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copied down.
    Now there is in columns C:D data for two series.
    max values are 3 hours+ for below limit and 6 minutes above limit. The graph is preppared that way (see attachment):

    step 1 column graph form C and D

    step 2: 2nd series drawn in secondary axis

    step 3: manual axis limits. For primary between -4 and 4 hours, for secondary between -8 and 8 minutes
    because for columns C and D we have max values of 3:17 and 00:06
    as we want "easy to see values, we will divide axis for C column into 8 half hour lengths (7 would be enough too),
    and to maintain "fit" between primary and secondary axes for data in D we will set axis between -8 and 8 minutes (one minute lengths)
    the division into half hour and one minute will be done in next step

    step 4: division into appropriate legths between labels on both aves, main axis in reversed order

    The numerical values for 1 minute, 8 minutes, 1/2 hour and 4 hours are calculated in L30:Q32

    See attached file.

    Hope it is clear now
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-22-2020
    Location
    Dubai
    MS-Off Ver
    365
    Posts
    66

    Re: chart to represent the value (below and above target )

    Quote Originally Posted by Kaper View Post
    The first part is quite obvious. As the time in excel cannot be negative, the formulas in C2 and D2 are respectively:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copied down.
    Now there is in columns C:D data for two series.
    max values are 3 hours+ for below limit and 6 minutes above limit. The graph is preppared that way (see attachment):

    step 1 column graph form C and D

    step 2: 2nd series drawn in secondary axis

    step 3: manual axis limits. For primary between -4 and 4 hours, for secondary between -8 and 8 minutes
    because for columns C and D we have max values of 3:17 and 00:06
    as we want "easy to see values, we will divide axis for C column into 8 half hour lengths (7 would be enough too),
    and to maintain "fit" between primary and secondary axes for data in D we will set axis between -8 and 8 minutes (one minute lengths)
    the division into half hour and one minute will be done in next step

    step 4: division into appropriate legths between labels on both aves, main axis in reversed order

    The numerical values for 1 minute, 8 minutes, 1/2 hour and 4 hours are calculated in L30:Q32

    See attached file.

    Hope it is clear now
    Many thanks cleared

+ 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. best way to represent the chart
    By geniusufo007 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-21-2020, 12:11 PM
  2. Chart to represent SQL Table Join
    By Phlegon_of_Tralles in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-17-2019, 04:38 PM
  3. What's the ideal way to represent this data in a chart?
    By Katie620 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-11-2018, 08:32 PM
  4. Best way to represent this data in excel chart
    By masimo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-17-2017, 08:37 PM
  5. Bar chart with 'target' lines but target varies per column
    By Ineedcoffee in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 11-24-2011, 05:41 AM
  6. best way to represent data in Chart(graph)
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2010, 02:36 AM
  7. Can't get chart to represent data correctly - 03 or 07
    By indi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-22-2009, 12:26 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