Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill in missing months
I have a table containing periodic rates at monthly intervals from 1970.
Not all months have numbers, so I want those months rates to equal the previous month's rate. How do I "fill in" these blanks? Example: 01/1985 - 25.00 02/1985 03/1985 04/1985 - 29.25 05/1985 06/1985 07/1985 - 19.00 08/1985 09/1985 I have to do this over and over again, so I need a routine. I need for 02/1985 to equal 25.00; and 05/1985 to equal 29.25, etc. Thanks, Pat Upshaw |
#2
|
|||
|
|||
Select the column with the numbers. Then Edit/Goto, click the Special button
and select Blanks. The active cell will be one of the blanks. In that cell type an equal sign (to start a formula), and point to the cell above. ExampleL if cell B2 is active, the formula will end up as =B1. Then press CTRL+ENTER, which will enter that formula in all of the blank cells. Then select all of the filled cells in that column, Edit/Copy them, then Edit/Paste Special, and select the Values option. On Wed, 8 Jun 2005 20:10:20 -0500, "Charles P. \(Pat\) Upshaw" wrote: I have a table containing periodic rates at monthly intervals from 1970. Not all months have numbers, so I want those months rates to equal the previous month's rate. How do I "fill in" these blanks? Example: 01/1985 - 25.00 02/1985 03/1985 04/1985 - 29.25 05/1985 06/1985 07/1985 - 19.00 08/1985 09/1985 I have to do this over and over again, so I need a routine. I need for 02/1985 to equal 25.00; and 05/1985 to equal 29.25, etc. Thanks, Pat Upshaw |
#3
|
|||
|
|||
Try this:
1)Select from the first rate through the last blank cell you want to use. 2)EditGo To [Special . . .], Blanks, then Click [OK] Leave the blank cells selected. 3)Type an equal sign (=) and press the up arrow one time. 4)Hold down the [Ctrl] key and press [Enter] If you want all the rate cells to be hard-coded (instead of formulas): Select the rate range, then EditCopy, followed by EditPaste SpecialValues Does that help? -- Regards, Ron |
#4
|
|||
|
|||
The easiest solution is to use a vlookup of the known values. I use the
fill procedure to establish all of the months. The known values are in another table with the corresponding months. I use vlookup to find the exact or previous value. Sorry I made this sound more complicated than it was. Thanks for the replies. Pat Upshaw "Charles P. (Pat) Upshaw" wrote in message ... I have a table containing periodic rates at monthly intervals from 1970. Not all months have numbers, so I want those months rates to equal the previous month's rate. How do I "fill in" these blanks? Example: 01/1985 - 25.00 02/1985 03/1985 04/1985 - 29.25 05/1985 06/1985 07/1985 - 19.00 08/1985 09/1985 I have to do this over and over again, so I need a routine. I need for 02/1985 to equal 25.00; and 05/1985 to equal 29.25, etc. Thanks, Pat Upshaw |
#5
|
|||
|
|||
How can it be easier to use formulas when you can do it in seconds using the
2 answers you got? -- Regards, Peo Sjoblom (No private emails please) "Charles P. (Pat) Upshaw" wrote in message ... The easiest solution is to use a vlookup of the known values. I use the fill procedure to establish all of the months. The known values are in another table with the corresponding months. I use vlookup to find the exact or previous value. Sorry I made this sound more complicated than it was. Thanks for the replies. Pat Upshaw "Charles P. (Pat) Upshaw" wrote in message ... I have a table containing periodic rates at monthly intervals from 1970. Not all months have numbers, so I want those months rates to equal the previous month's rate. How do I "fill in" these blanks? Example: 01/1985 - 25.00 02/1985 03/1985 04/1985 - 29.25 05/1985 06/1985 07/1985 - 19.00 08/1985 09/1985 I have to do this over and over again, so I need a routine. I need for 02/1985 to equal 25.00; and 05/1985 to equal 29.25, etc. Thanks, Pat Upshaw |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel printing problem - won't print cell under a "fill effect" bo | Excel Discussion (Misc queries) | |||
Fill colour red | Excel Discussion (Misc queries) | |||
Missing lines in chart w/x-axis with months 1-24... | Charts and Charting in Excel | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |