I have a list of data, fill in the gaps. FILL function won't work
I have a list of data in which I would like to fill in the gaps. Is this
possiable? I have age in years in A2 to A100 then data in colums in 5 year gaps (eg B2 has data then B7 then B12 etc) all incremental. I would like to fill in the gaps belween giving me data B2, B3, B4... to B100. How can I fill in the gaps whilst still making sure my 5 year results are left. Simple example below age Result 45 20 46 47 48 49 50 30 51 52 53 54 55 50 Many Thanks for any help |
You could select a range (B2:B7 for 20 to 30).
Then do Edit|fill|series There's an option in there for linear. Or you could click the Trend box and use Linear or Growth. This does the a linear version for each step in code: Option Explicit Sub testme() Dim TopCell As Range Dim BotCell As Range Dim FirstRow As Long Dim myStep As Double With ActiveSheet FirstRow = 2 Set BotCell = .Cells(.Rows.Count, "B").End(xlUp) Do Set TopCell = BotCell.End(xlUp) myStep = (BotCell.Value - TopCell.Value) _ / (BotCell.Row - TopCell.Row) With .Range(TopCell, BotCell) .DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=myStep, _ Trend:=False End With Set BotCell = TopCell If BotCell.Row <= FirstRow Then Exit Do End If Loop End With End Sub It does assume that you don't have any values in column B that are adjacent--there always has to be a gap. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Triv wrote: I have a list of data in which I would like to fill in the gaps. Is this possiable? I have age in years in A2 to A100 then data in colums in 5 year gaps (eg B2 has data then B7 then B12 etc) all incremental. I would like to fill in the gaps belween giving me data B2, B3, B4... to B100. How can I fill in the gaps whilst still making sure my 5 year results are left. Simple example below age Result 45 20 46 47 48 49 50 30 51 52 53 54 55 50 Many Thanks for any help -- Dave Peterson |
All times are GMT +1. The time now is 02:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com