Skip navigation

chart from pop up menu values

385 Views 7 Replies Latest reply: Nov 10, 2012 8:51 PM by Barry RSS
ourgouldfamily Calculating status...
Currently Being Moderated
Nov 9, 2012 1:17 PM

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)
  • Wayne Contello Level 6 Level 6 (12,830 points)
    Currently Being Moderated
    Nov 9, 2012 1:48 PM (in response to ourgouldfamily)

    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.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 9, 2012 11:19 PM (in response to ourgouldfamily)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 10, 2012 11:57 AM (in response to ourgouldfamily)

    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

  • Wayne Contello Level 6 Level 6 (12,830 points)
    Currently Being Moderated
    Nov 10, 2012 12:30 PM (in response to ourgouldfamily)

    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/

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 10, 2012 8:51 PM (in response to ourgouldfamily)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.