+ Reply to Thread
Results 1 to 8 of 8

Creating a "difficult" or "simple" bar chart

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    9

    Creating a "difficult" or "simple" bar chart

    Hi all,

    First I wanted to create as I descripe an difficult chart to make:

    I have a list of change records with 2 different date columns (start date & close date).
    I want a bar chart that shows me on a weekly base how many change records were still open. So for every week the chart has to check if a records has a startdate that starts in that same week or before. If it has it should count it otherwise ignore it. Also for all the records it finds as just stated the chart should check if the record has a close date that is within the same week or before. If is has than that record should also be ignored.

    I really can't figure out how to create this kind of chart.

    ----------------------------------

    When I did not know how to make the above chart I tried to create the followling chart:

    I have a list of change records with 2 different date columns (start date & close date).
    Now I want to create a bar chart that show me on a weekly base how many change records were created and how many were closed that week.

    I also don't know how to figure this one out.

    ----------------------------------

    Please help me, I really would like the first chart, but if not possible please help me with the second one. If you need for information please ask and I willl provide it were I can.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Creating a "difficult" or "simple" bar chart

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-28-2014
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a "difficult" or "simple" bar chart

    This is an exaple of first chart (the difficult one) with partial real data. Hope this helps.
    Attached Files Attached Files
    Last edited by NLatuny; 03-23-2016 at 08:23 AM.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Creating a "difficult" or "simple" bar chart

    you dates dont quite make sense in column J

    they are just weeks
    but the first starts on a friday and others on a saturday - is that a typo

    those dates can be created automatically but how will excel know when to start the week?
    can it be based from column B perhaps look at the minimum date and choose the saturday before that

    Then looking at your count - is that using the info from B and C
    the item with 7
    you dont have 7 items that started after 26/3/2015

    row 8,9,10 & 11 starts after 26/3/15 so thats a max of 4
    and none closed in that week - so still 4

    what am i missing on the counts

  5. #5
    Registered User
    Join Date
    11-28-2014
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a "difficult" or "simple" bar chart

    Yes column J are just weeks, as I want to know the number of open changes each week (from thursday till wednesday). This is all 2015 data.
    How does excel know what week to start? well based on the first date in column B I think.

    the 7 is correct as in that week there were 7 changes still open (open = not having a closure date or having a closure date that is later than 1-4-2015). You also have to count the records that were created before that week that still havent been closed.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Creating a "difficult" or "simple" bar chart

    yep, looking at the calendar - i mixed up the 2015 and 2016 !!!!

    I have created the first week using
    =TEXT(((4-WEEKDAY(MIN(B:C),2))+MIN(B:C)-7),"DD-MM-YYYY")&" - "&TEXT(((4-WEEKDAY(MIN(B:C),2))+MIN(B:C)-7)+6,"DD-MM-YYYY")

    can you just list the cell references for the 7 ie A9, a10 as they started after 26/3/15 but before the 1/4/15
    and Bx ,Bx ,Bx ,Bx, Bx - because ?????

    cant get my head around the 7 yet

    with a couple of helper columns - which we will also need for the counts - once I understand
    heres the sample for creating the X-Axis automatically

    As i'm away now for a while today
    Attached Files Attached Files
    Last edited by etaf; 03-23-2016 at 09:29 AM.

  7. #7
    Registered User
    Join Date
    11-28-2014
    Location
    Rotterdam
    MS-Off Ver
    2010
    Posts
    9

    Re: Creating a "difficult" or "simple" bar chart

    Thank for your help etaf, I really appreciate it. However, I already got myself a solution as I leared about the COUNTIFS formula.

    Thanks again.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Creating a "difficult" or "simple" bar chart

    thanks for letting us know

+ 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. Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Problem creating Line Chart from columns "date" and "money"
    By brosef in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-05-2012, 09:01 AM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

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