Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keep rows with most recent date | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
output date modified if data is entered for multiple rows | Excel Discussion (Misc queries) | |||
Numbering of Grouped Rows | Excel Discussion (Misc queries) |