Dynamic Chart Source Data

[ajgillert]

I work in retail and am currently creating a spreadsheet for Rosters. The user enters StartTime / EndTime for an employees shift. This data is then collated into a table like this:

Monday
Employee1 09:00 17:30
Employee2 09:00 17:30
Employee3 10:00 16:00
Employee4 09:00 15:00
etc..

I then calculate how many staff are working at a particular time:
Monday
00:00 #N/A
...
08:00 #N/A
09:00 3
10:00 4
11:00 4
12:00 4
13:00 4
14:00 4
15:00 3
16:00 2
17:00 2
18:00 #N/A
...
24:00 #N/A

This all works perfectly fine.
I am then trying to chart this data (with time along the x-axis and number of staff on the y-axis). I avoid charting the 0 values by using a table containing #N/A values.

The issue with the graph is that the time axis always display a full 24 hour period, 00:00 thru to 23:59 (the table needs to allow for a whole 24 hour period as some outlets could be trading all day). For many stores,
this leaves the data looking somewhat 'lonely' in the middle of the chart with large gaps between 00:00 to 09:00 and 18:00 to 23:59.

I've tried to create a table where the time value is #N/A if the number of staff is #N/A (0). However, as excel is using the time for the x-axis it seems to insist on charting the #N/As on the axis regardless.

I need some way to make the 'Source Data' dynamic such that it only encompasses the none #N/A values (ie 09:00 to 17:00 in the above example).

Any help would be greatly appreciated.

 

[GlennUK]

Yes, make a couple of dynamically defined named ranges for the titles and the data. Do menu command Insert/Name/Define, and make a name of DataTitles, with a definition like:

=OFFSET(Sheet2!$A$1,MATCH(1,IF(ISNA(Sheet2!$B$2:$B$25),0,1),0),0,COUNTIF(Sheet2!$B$2:$B$25,">0"),1)

and another one named DataSeries:

=OFFSET(Sheet2!$B$1,MATCH(1,IF(ISNA(Sheet2!$B$2:$B$25),0,1),0),0,COUNTIF(Sheet2!$B$2:$B$25,">0"),1)

and use those names in the chart, like Sheet2!DataTitles and Sheet2!DataSeries.

 

[ajgillert]

Thanx for the help.

The above suggestion works to a degree. Effectively I end up with the set of #N/As at the end of the list removed (on the x-axis), but not those at the beginning (unless I'm doing something wrong).
So effectively end up with this:

00:00 #N/A
...
08:00 #N/A
09:00 3
10:00 4
11:00 4
12:00 4
13:00 4
14:00 4
15:00 3
16:00 2
17:00 2
NOTHING ELSE HERE

Things move on, I now feel it would be better to duplicate the list and remove the #N/A entries in this duplicated list.
To simply end up with:

09:00 3
10:00 4
11:00 4
12:00 4
13:00 4
14:00 4
15:00 3
16:00 2
17:00 2

I realise that this involves some kind of
=INDEX(range,Match(

But can't get seem to figure this out.

Any help? Please.

 

[GlennUK]

No, you are doing something wrong. The formulas get rid of #N/As at the beginning and the end. Can you recheck everything?

 

[ajgillert]

I'm pretty certain I've got everything correct.

I've cut'n'pasted the data for Sunday to another sheet to make the following explanation easier.

The data is in the range A1:B50 and looks like this:

Row
01 BLANK SUN
02 #N/A #N/A
03 #N/A #N/A
04 #N/A #N/A
05 #N/A #N/A
06 #N/A #N/A
07 #N/A #N/A
08 #N/A #N/A
09 #N/A #N/A
10 #N/A #N/A
11 #N/A #N/A
12 #N/A #N/A
13 #N/A #N/A
14 #N/A #N/A
15 #N/A #N/A
16 #N/A #N/A
17 #N/A #N/A
18 08:00 1
19 08:30 1
20 09:00 2
21 09:30 2
22 10:00 3
23 10:30 3
24 11:00 4
25 11:30 4
26 12:00 4
27 12:30 4
28 13:00 4
29 13:30 4
30 14:00 4
31 14:30 4
32 15:00 4
33 15:30 3
34 16:00 3
35 16:30 2
36 17:00 2
37 17:30 2
38 #N/A #N/A
39 #N/A #N/A
40 #N/A #N/A
41 #N/A #N/A
42 #N/A #N/A
43 #N/A #N/A
44 #N/A #N/A
45 #N/A #N/A
46 #N/A #N/A
47 #N/A #N/A
48 #N/A #N/A
49 #N/A #N/A
50 #N/A #N/A

The #N/As in column A are generated as follows eg for 09:00
=IF(B20<1,NA(),9/24)

with the cells in column A set to Custom hh:mm (I've also tried it with the cells set to Time).

I create a named range called R_Titles thus:
=OFFSET(Sheet1!$A$1,MATCH(1,IF(ISNA(Sheet1!$B$2:$B$50),0,1),0),0,COUNTIF(Sheet1!$B$2:$B$50,">0"),1)

and one named R_Series_Sun thus:
=OFFSET(Sheet1!$B$1,MATCH(1,IF(ISNA(Sheet1!$B$2:$B$50),0,1),0),0,COUNTIF(Sheet1!$B$2:$B$50,">0"),1)

to verify, when i click, Insert > Name > Define > R_Titles, then click the formula in the 'Refers to:' box 'marching ants' are displayed around A18:A37 as expected.

likewise, clicking R_Series_Sun then in the formula in the 'Refers to:' box
'marching ants' are displayed around B18:B37 as expected.

I then click Insert > Chart, XY (Scatter), Next, and put '=R_Titles,R_Series_Sun' in the Data Range box, and check 'Series in: Columns'

The x-axis of the chart still starts at 00:00 NOT 08:00.

If I click Finish, right click on the chart and click 'Source Data' the 'Data range:' now states:

=Sheet1!$A$18:$B$37

In other words, then named range in the chart is being replaced with an Absolute reference !?!

 

[GlennUK]

X-Y scatter always tries to make the origin 0,0 - use a different chart type, like line ( no line, just points ). Don't put your references in the Data Range tab, use the Series tab, and they won't convert to absolute references from names.

 

[ajgillert]

Got it.

Thanx very much for your help.

Guess it ultimately boiled down to not understanding the quirks of Excel.

 

[GlennUK]

Glad you got it working. Excel can be a bit awkward sometimes ... you get to the know the quirks in the long run.