7 Replies Latest reply: Nov 10, 2012 8:51 PM by Barry
ourgouldfamily Level 1 Level 1 (0 points)

I have a table that lists project names on the Y axis and a month/year designation on the X axis.  For each project, I have a status assigned for each month.  The statuses are selected from a custom pop-up menu in the Numbers table. 

 

I would like to create a bar chart that uses the same Y and X axis labels, but designate a different color for each status and have it displayed on the bar.  Attached below is a graphic of a manually updated table in Excel that I would like to automate in Numbers.

 


iMac, Mac OS X (10.6.3)
  • 1. Re: chart from pop up menu values
    Wayne Contello Level 6 Level 6 (13,620 points)

    ourgouldfamily

     

    the graphic is not showing up.  Can you posting use a png format like the result of using the key combination:

    <COMMAND> + <SHIFT> + 4

     

    once you type the key combination the cursor will change to a crosshair.  Click, and hold, in the top-left corner, then drag to the bottom-right corner, then release.  The screenshot will be on the desktop.

  • 2. Re: chart from pop up menu values
    Barry Level 7 Level 7 (29,180 points)

    I would like to create a bar chart that uses the same Y and X axis labels, but designate a different color for each status and have it displayed on the bar.  Attached below is a graphic of a manually updated table in Excel that I would like to automate in Numbers.

     

    Your tool for this will be a Stacked Bar Chart.

     

    Numbers assigns a single colour to each series of data. You can change the assigned colours, but the change applies to all data in the same series.

     

    To use a different colour for each "status", you will need to place the values for each status into a different series(column).

     

    Assuming the pop-up menu containing the status information is in column A, the value to be charted in column B, and the status label naming the status/category to be recorded in a column is in row 1 of that column, your formula for the first choice, placed in column F for the example, would be something like:

     

    F2: IF($A2=F$1,$B2,"")

     

    Fill right for as many columns as there are choices in the pop-up menu, and down for as many rows as there are numerical data to be graphed.

     

    Regards,

    Barry

  • 3. Re: chart from pop up menu values
    ourgouldfamily Level 1 Level 1 (0 points)

    Wayne,

     

    Sorry about the graphic.  Here's my second attempt.

     

    Screen Shot 2012-11-10 at 11.29.15 AM.png

    This table was originally made in Excel and color-coded by hand, rather than simply assigning a value to a project(column A) and month (Row 1)

     

    Erin

  • 4. Re: chart from pop up menu values
    ourgouldfamily Level 1 Level 1 (0 points)

    Barry,

     

    Sorry, I'm not sure I followed your description.  Does the graphic I attached for Wayne change your instructions?

  • 5. Re: chart from pop up menu values
    Barry Level 7 Level 7 (29,180 points)

    ourgouldfamily wrote:

     

    Barry,

    Does the graphic I attached for Wayne change your instructions?

    Yes. I was thinking in terms of a chart/graph rather than a table. Conditional formatting should work here, although display of the words in the bars would need to be omitted or applied manually. Will have a chance to take a closer look later in the day or evening, so I'll check in again then.

     

    Regards,

    Barry

  • 6. Re: chart from pop up menu values
    Wayne Contello Level 6 Level 6 (13,620 points)

    Isn't this a Gant-type chart?  This is best done using a program like:

    For Purchase:

         MSProject

         OmniPlan: from Apple App store

         XPlan: from Apple App store

         PlanIt: from Apple App store

         SG Project Pro:  Apple App store

         Project Planner:  Apple App store

         iTaskX:  Apple App store

     

     

    For Free:

          OpenProj:  http://www.serena.com/products/openproj/

  • 7. Re: chart from pop up menu values
    Barry Level 7 Level 7 (29,180 points)

    Wayne's suggestions regarding software dedicated to this purpose is well taken. Here are some further thoughts on using Numbers to produce a GANT-like chart.

     

    The first is similar to what you appear to have done in Excel—each month is represented by a coloured cell. In the example below, I've used a pop=up menu in each cell to select one of the stages in your projects, and conditional formatting to set the colour of the cell and the text according to the choice made.

    Picture 20.png

    I chose slightly different colours for the text and fill so that the text resulting in the colours can be seen. Pretty much a hands-pn do-it-yourself solution; no bells or whistles at this point.

     

    The second, below is a bit closer to automated, but has some flaws, nted below the images.

    "Data" is the table on which the start dates for each stage are recorded. Where one stage is skipped (as in Project 2) the 'start' date for that stage (evaluation) is set to the same date as for the next stage.

    Picture 23.png

    For chart is an auxiliary table, where DATEDIF(start,finish) is used to calculate the number of days between beginning and end of each stage. The chart is a Stacked Bar Chart, with the first bar's fill set to 'none'.

     

    For Chart uses three fomulas:

     

    A2: =Data :: A2

    This copies the project name from the first column of "Data". Fill down from A2.

     

    B2: =IFERROR(DATEDIF($A$1,Data :: D2,"D"),"")

    Fill down to the end of column B.

     

    A1 of this table contains the date and time value for the last day of the previous year (Dec 31, 2011 00:00:00). I've left this displayed , but it could be hidden.

    Data::D2 contains the start date for the first stage (Exploring) of the project.

     

    C2: =IFERROR(DATEDIF(Data :: D2,Data :: E2,"D"),"")

    Fill down the rest of column C, and right to column F (Delivery).

     

    This is the same formula as in column B. It differs only in the cell references containing the dates between which the difference is calculated.

     

    The chart is constructed by selecting cells B2:F5 on For Chart, then choosing the (horizontal) stacked bar chart from the Chart button's menu. Click the three bars icon that appears at the top left of the selected cells to change from 'series in column' to 'series in row'.

     

    Limitations:

    Except for a scatter chart,Numbers charts cannot use Date and Time values on a Value axis. Here, I've used numbers representing the day-of-year number.

    Numbers permits a maximum of 10 divisions on a Value axis, which isn't enough to break a year into months. The best possible, for appearance, is to set the maximum to 360 days, then use six divsions, each 60 days, to place the vertical grid lines.

    If you also fix the size of the chart, you could replace these numbers along the x axis with the equivalent dates, entered into one or more text boxes, then spaced to match the chart's vertical grid lines (and points between these lines).

     

    An interesting exercise, but not, I'm afraid, a fantastic solution.

     

    Regards,

    Barry