consecutive date numbering by rows of 4
 

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

consecutive date numbering by rows of 4
 

Hi

Enter your start date in A1 01/01/07
in A2
=IF(MOD(ROW(),4)=1,A1+7,A1)
FormatCellsNumberCustom dd/mm/yy
copy down as far as required
--
Regards

Roger Govier


"doinwork" wrote in message
...
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

Plotting empty cells does not work as said by help
 

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.

Plotting empty cells does not work as said by help
 

Hi

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

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

--
Regards

Roger Govier


"Bernd Pollermann" wrote in message
.ch...


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.

Plotting empty cells does not work as said by help
 

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

Best regards, Bernd






On Thu, 25 Jan 2007, Roger Govier wrote:

Hi

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

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

--
Regards

Roger Govier


"Bernd Pollermann" wrote in message
.ch...


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.

Plotting empty cells does not work as said by help
 

Hi Bernd

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

Roger Govier


"Bernd Pollermann" wrote in message
.ch...

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

Best regards, Bernd






On Thu, 25 Jan 2007, Roger Govier wrote:

Hi

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

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

--
Regards

Roger Govier


"Bernd Pollermann" wrote in message
.ch...


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.