5 Replies Latest reply: May 28, 2010 1:46 AM by Barry
stvelucky Level 1 Level 1 (5 points)
I have a line chart that lists sales by day and an overall average (as seen below):

!http://dl.dropbox.com/u/302167/Screen%20shot%202010-05-27%20at%202.38.04%20PM.p ng!

I want to color the background based on the day. I would like Mon-Fri. to be one solid color while Sat & Sun are a different color. This will help me to visually see how my sales did on the weekend vs. weekdays. I thought the easiest way might be to create a second bar chart and place it behind my line chart. But I wasn't sure how to auto format it so that the weekend bars are a different color. This chart and accompanying table will be copied over for each month so the weekend days won't always be in the same spots and I would like to have as little manual interaction with the chart (hopefully, none) as possible. Does that make sense?

iMac CoreDuo, Mac OS X (10.5.8)
  • Barry Level 7 Level 7 (29,210 points)
    stvelucky wrote:
    I have a line chart that lists sales by day and an overall average

    I want to color the background based on the day. I would like Mon-Fri. to be one solid color while Sat & Sun are a different color. This will help me to visually see how my sales did on the weekend vs. weekdays. I thought the easiest way might be to create a second bar chart and place it behind my line chart.


    Right idea. Instead of a second chart, I used a single line table, placed behind the chart. The image also contains a duplicate of this table, which you won't need.

    Photobucket

    The selected table, 2-1, is the duplicate of the one placed behind the chart to highlight the weekend days (May 1, 2, 8, 9). In the duplicate, I've left the 'standard' cell background (white) and text colour (black), and set the conditional text colour to red and fill colour to a light yellow so that the value contained in each cell is visible.

    In the 'real' Table 2, Fill and Text colours are both set to white (choose which ever colour you want for weekdays) and conditional Fill and Text colours are both set to the same shade of yellow (again, choose whichever colour you want for weekends) to make the text invisible.

    the rule (condition) used for all cells in Table 2 is "Equal to" 1.

    The fill colour of the Chart is set to 'none' (top left cell in the colour palette shown when you click the Fill color well) to allow table 2 to show through when it is placed behind the chart.

    The formula in all cells of Table 2 is:

    =IF((OR(DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Saturday",DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Sunday")),1,0)

    which is also shown in the text box on the screen shot.

    The formula requires that the dates in column A of Table 1 be actual dates, and that they be in column A.

    Table 2 is a single row, plain style table (no header rows, no header columns). Resize it by selecting the table to get the handles shown on table 2-1 in the screen shot, then drag the handles to make the table wide enough to center the columns on the data points of the chart, and to make the height of the table cover the chart (and if desired, the x-axis labels).


    Regards,
    Barry
  • stvelucky Level 1 Level 1 (5 points)
    This works PERFECTLY! A table! Why didn't I think of that? That makes total sense. Only one question, in some of the rows on Table 1, the date is blank which returns an error on my new colored date chart. Is there a way to make it suppress the warnings?

    Message was edited by: stvelucky
  • Barry Level 7 Level 7 (29,210 points)
    stvelucky wrote:
    In some of the rows on Table 1, the date is blank which returns an error on my new colored date chart. Is there a way to make it suppress the warnings?


    Enclose the formula in an IFERROR() statement.

    =IFERROR(IF((OR(DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Saturday",DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Sunday")),1,0),0)

    With the last value set as 0, the no-date columns will have the same colour as weekdays. If you want a different colour, use a different value ( eg. end the formula with " ),-1)" ), and set a second conditional rule to set Fill and Text colours to the 'different colour' when the cell is 'equal to -1'.

    Regards,
    Barry
  • stvelucky Level 1 Level 1 (5 points)
    That did it. Next item on my Numbers "to learn" list: the IFERROR() statement. Thanks for your help!
  • Barry Level 7 Level 7 (29,210 points)
    Thanks for the feedback.

    IFERROR() is quite useful (as you'll see when checking some of the examples in the forum).

    Regards,
    Barry