consecutive date numbering by rows of 4

[Guest]

Hi
I can consecutively number dates down a column from 01/01/07, 08/010/7 -
31/12/07, using the edit fill series process.

I need each 7 day date to repeat down column A by 4 rows

eg:
A1 = 01/01/07
A2 = 01/01/07
A3 = 01/01/07
A4 = 01/01/07
A5 = 08/01/07
A6 = 08/01/07
A7 = 08/01/07
A8 = 08/010/7
A9 = 15/01/07 etc

the only way I can do it at the moment is to enter 01/01/07 in A1 and fill
down to the A4, enter 08/01/07 in cell A5 and fill down to A8 etc

is there a step repeat or edit fill that will do the above process by 4 rows?

thanks for your assistance

 

[Roger Govier]

Hi

Enter your start date in A1 01/01/07
in A2
=IF(MOD(ROW(),4)=1,A1+7,A1)
Format>Cells>Number>Custom> dd/mm/yy
copy down as far as required

 

[Bernd Pollermann]

Hallo,

My aim is to have gaps in a chart when a cell is empty.
So, what I did was the following:

In the column to be plotted (E) I put the formula:

=if(I4=0,"",I4)
=if(I5=0,"",I5)
=if(I6=0,"",I6)

(in column 'I' I have my data, which as of a certain row
are 0, and which I don't want to see in the chart)
As a consequnce the cells in columns 'E' do look empty
if the content in columns 'I' is 0.


I then clicked on my chart and went to Tools -> Options -> Chart and
selected the option

Plot empty cells as: * Not plotted (leave gaps)

and clicked OK, in other words I followed to the letter the
directives in Excel (I am using Excel 2003 from Windows XP).


Despite these efforts, the empty cells are plotted as zero-values.

Any ideas of what I can do to create empty cells which are
"respected: by the chart?

Best regards, Bernd

Ps. In case I "delete" the content of the cell manually
I get the gap in the chart. This seems to suggest that
the "" in my if-formula does not produces really an empty cell
as far as the chart goes.

 

[Roger Govier]

Hi

Try using NA() instead
=if(I4=0,NA(),I4)

The chart will not treat the #N/A resulting output as a zero value

 

[Bernd Pollermann]

Thank you very much, this indeed works.
Just a pity as far as aesthetics are concerned.

Best regards, Bernd

 

[Roger Govier]

Hi Bernd

If you are worried about the aesthetics, just use Conditional formatting
Mark the range where your #N/A's are being returned
Format>Conditional Formatting>dropdown for Formula is
Formula =I4=0 (or whatever you used to generate the NA())
Format Font>White (or same as background colour)