Filling cells in between pairs of numbers

Hello all,

I am trying to find an easy way to fill in cells in between pairs of
numbers each of which is exactly the average of the pair's difference
averaged by the number of these cells greater than the previous.

So I have
1 1
2
3
4 8
5
6
7
8 20
9
10
12
13
14
15 45

I want:
1    1
2     3.333333333
3     5.666666667
4     8
5     11
6     14
7     17
8     20
9     23.57142857
10   27.14285714
11   30.71428571
12   34.28571429
13   37.85714286
14   41.42857143
15   45

I know I can do a Fill, Series, but then even with a macro that does a
"control shift up fill series enter", it would take me forever for I
have easily 100,000 such entries.

Is there any faster ways?

Thanks a million!
0
NiKSOsf (1)
6/29/2008 3:11:00 AM
excel 39879 articles. 2 followers. Follow

1 Replies
647 Views

Similar Articles

[PageSpeed] 32

Give this macro a try (just set the start row for your first piece of data 
in the StartRow constant and the column where you data is at in the 
DataColumn constant at the beginning of the macro)...

Sub FillAverages()
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Dim C As Range
  Dim X As Long
  Dim CurRow As Long
  Dim CurVal As Double
  Dim Average As Double
  Set C = Cells(StartRow, DataColumn)
  Do While C.End(xlDown).Row < Rows.Count
    CurVal = C.Value
    CurRow = C.Row
    Set C = C.End(xlDown)
    Average = (C.Value - CurVal) / (C.Row - CurRow)
    For X = CurRow + 1 To C.Row - 1
      Cells(X, DataColumn).Value = Cells(X - 1, DataColumn).Value + Average
    Next
  Loop
End Sub

Rick


"Nik" <NiKSOsf@gmail.com> wrote in message 
news:3964c324-f42f-497c-9c19-3c1132607be3@e53g2000hsa.googlegroups.com...
> Hello all,
>
> I am trying to find an easy way to fill in cells in between pairs of
> numbers each of which is exactly the average of the pair's difference
> averaged by the number of these cells greater than the previous.
>
> So I have
> 1 1
> 2
> 3
> 4 8
> 5
> 6
> 7
> 8 20
> 9
> 10
> 12
> 13
> 14
> 15 45
>
> I want:
> 1    1
> 2     3.333333333
> 3     5.666666667
> 4     8
> 5     11
> 6     14
> 7     17
> 8     20
> 9     23.57142857
> 10   27.14285714
> 11   30.71428571
> 12   34.28571429
> 13   37.85714286
> 14   41.42857143
> 15   45
>
> I know I can do a Fill, Series, but then even with a macro that does a
> "control shift up fill series enter", it would take me forever for I
> have easily 100,000 such entries.
>
> Is there any faster ways?
>
> Thanks a million! 

0
6/29/2008 4:10:59 AM
Reply:

Similar Artilces:

Conditional formatting with cell styles
I like the cell styles and the fact that they can change if I change a workbook theme. I'd like to use these styles in conditional formats but I can only see manual settings for fonts, borders, fills etc in the conditional formatting options. Is there a way to specify that a conditional format should use a named cell style perhaps? Thanks for any clues you can give. Kevin ...

Filling cells in between pairs of numbers
Hello all, I am trying to find an easy way to fill in cells in between pairs of numbers each of which is exactly the average of the pair's difference averaged by the number of these cells greater than the previous. So I have 1 1 2 3 4 8 5 6 7 8 20 9 10 12 13 14 15 45 I want: 1 1 2 3.333333333 3 5.666666667 4 8 5 11 6 14 7 17 8 20 9 23.57142857 10 27.14285714 11 30.71428571 12 34.28571429 13 37.85714286 14 41.42857143 15 45 I know I can do a Fill, Series, but then even with a macro that does a "control shift up fill series enter",...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

Case filled out from SharePoint
I need to have a simple form my users fill out to submit a case. This would include the Business Required fields on the CRM Case form. Is there an easy way to do this using SharePoint? In other words, the user would go to the SharePoint site, click a link to "Submit a Support Request". This would open a form (Infopath perhaps?) where they fill in a few simple items, click submit, and a new CRM Case is created. This then would fire any pertinent workflows as if the case had been created from within CRM itself. I'm aware there are 3rd party solutions out there but would...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

trim spaces in a cell
How do i trim empty spaces in a cell containing an invoice number but the length of the invoice number in column B is of dynamic length? For example, Column B 123 4567-------->1234567 987 3------------>9873 5 55------------->555 Thanks. Ringo Tan Ringo If there is only ever one space then the worksheet function below should work =REPLACE(A1,FIND(" ",A1,1),1,"") -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "ringo tan" <ringotan@discussions.microsoft.com> wrote in message news:F8741...

From field filled in
Hi and thanks in advance for this help. I am using Office 2003 with Exchange 2003. I have a second mailbox setup in my profile. When I reply to an e-mail sent to the other inbox, the From field is automatically filled in with that name. So for example, if the other inbox is "HelpDesk" and I reply to an e-mail in that inbox, the from field is automatically filled in as "HelpDesk". Hope that makes sense. Thanks!! <twytch111@gmail.com> wrote in message news:1175611189.053649.28340@q75g2000hsh.googlegroups.com... > Hi and thanks in advance for this help. I am...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Cell entry
Want to input data A1, B1 & C1 on sheet 1, which will be save / store on sheet 2 at A1,B1, and C1, again I want to input new data on sheet 1 at A1,B1 and C1 which will be automatically save / store on Sheet 2 at A2, B2, and C2, repeatedly I want to input another new data on sheet 1 at A1, B1, & C1, whcih will be again save on sheet 2 at A3, B3, & C3 and so on. Means input cell (A1) on sheet 1 will be same but output data on sheet 2 will be A1,A2,A3 and so on. Rdgss........Sudipta Press ALT+F11 to get to VBA in Project - VBAProject window double click on Sheet1 sel...

format cells option not working
when i highlight some cells and try to format them nothing happends, workbook and sheets are not protected, cells in question dot not have any data in them at all, totaly blank! if they are locked how can i findout? and how to unlock them? Thanks, Steve Maybe this? OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/default.aspx?scid=kb;en-us;320531 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" <me@where.why> wrote in message news:K3Yfc.2505$UG1.744@newsfe5-gui.server.ntli.net... > when i highlight some cells and...

Operating fill handle and moving entire cells with keyboard
Is there any way of operating the fill handle or moving entire cells/range of cells with the keyboard rather than using the mouse (drag and drop). Thanks, Tom. Tom Copy or Cut Select range Select top left corner of range. Hold Shift & press End, Arrow down or right. Copy:= Ctrl + C Cut:= Ctl + X Paste Ctl + V To Fill Down or Right Ctrl + D or ctrl + r Use Excel Help and search for keyboard shortcuts for a complete List. Regards Peter >-----Original Message----- >Is there any way of operating the fill handle or moving >entire cells/range of cells with the keyboard rathe...

Display Row Number in Datasheet View
Can someone tell me how I can display the row numbers in a datasheet view when a query is run? I have looked and looked and can not figure out where and how I can do that. I just want it to display like a spreadsheet would with the row and column numbers. On Thu, 11 Feb 2010 12:07:02 -0800, garvic82 <garvic82@discussions.microsoft.com> wrote: >Can someone tell me how I can display the row numbers in a datasheet view >when a query is run? I have looked and looked and can not figure out where >and how I can do that. I just want it to display like a spreadsheet ...

Vlookup based on Active cell
Is there a way to have a vlookup formual based on the data in the active cell. I have data on one sheet that I what to pull in to a cell on another worksheet based on the data in the active cell I click on. I looking for the reference of the vlookup to be the active cell. thanks in advance for any help on this Thomp <williamth...@gmail.com> wrote... >Is there a way to have a vlookup formual based on the data in the >active cell. I have data on one sheet that I what to pull in to a cell >on another worksheet based on the data in the active cell I click on. >I looking for the...

Colours changing in the fill palette and the spreadsheet?
Hi I have a user with Office 2003, and her pc and build is standard as with her colleagues. In Excel the colours would change (cells filled) also if the user goes to her pallette the colours have also changed. Desktop and all other applications colour are fine. The following resolutions have been tried - office detect and repair, updates, asked the user to got to the tools options and reset the pallette and this does not work. She has to log off and back in again in order to set it back to the proper colours. Again this is only affecting Excel? Any pointers? I wish to thank you in advanc...

Ref cell in another worksheet
Hi All Sorry for the very basic question. I have several worksheets in a workbook and have named the first sheet 'Info'. This sheet holds such data as 'contract number' and 'user name'..... This data is required on all the other sheets but i would like to just reference the data from the 'Info' sheet What do i have to enter on the other sheets so that this info only has to be entered on the 'Info' sheet How do i reference this data/info from the 'Info' sheet in the header/footer of printed sheets Thanks for any/all help Sal Select the sheet...

Have one fill to auto fill aother
My question is simple. I would like to have field 27 = field 25. So when someone enters the data for field 25, the same data auto populated in field 27. Any help is appreciated! Kaylen, In the CotrolSource for Field 27 type... =[field 25] ....assuming of that you are talking about a form in Microsoft Access or did you mean to post this question in the Excel newsgroup? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Kaylen" <Kaylen@discussions....

How do you fix a cell number in excel for mac
Hello, How can i fix a cell in excel for mac. I am a PC user and i allways used the F4 key, but i cant find it for excel for mac. -- Muchas Gracias, Martin From Mac XL Help ("Keyboard Shortcuts" topic): CMD-T Toggle the formula reference style between absolute, relative, and mixed. In article <9FE8FA7E-D0C1-42D4-900E-6B902466F999@microsoft.com>, Martin <Martin@discussions.microsoft.com> wrote: > Hello, > How can i fix a cell in excel for mac. I am a PC user and i allways used the > F4 key, but i cant find it for excel for mac. ...

Excel: Remove characters from cells using wildcards
Can you anyone help me with this problem please? I have a column of data like the one below and I would like to remove the the front part, (x) from the cell, but I don't want to remove the parts in bracket that come later in the cell, for example (Queensland). I tried using Replace (**), and it did remove the first set of brackets and its contents but unfortunately it also removed the second set of brackets and its contents. If it helps in the first set of bracket, (x), x is always a number . While in the second set always contains a word. Before: (1) ABC Far North (Queensland) 0630 New...

Protecting specific cells from recalculation.
Hi I know I can set calculations to be manual or automatic but can highlight a bunch of cells and make them manual recalculation onl while leaving the rest of the sheet on automatic? The reason being I generate random numbers then have a drop down lis in other cells to assign those numbers, but the drop down list remove options as they are used (the list gets exhausted) to preven duplication / re-use of the generated numbers when they are assigned this requires automatic updates to work, but when that happens th random numbers get regenerated. So can I lock those numbers somehow, protecti...

Filling in blank PO
Hi everybody, I have some question to you, when you create blank PO, then go to content and click Add Item you can see tw boxes. In top one there are all items from your database and in bottom one there are Items you are adding. If you reach number of rows visible in bottom window there is scroll bar comming on right hand side, and as you add more items every new ona is going to the very bottm of the list which is in invisible area. To get there and type in Qty you have to scroll down whole list every time when you add next item. I'm using most recent updates for RMS and I checked i...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

I need a message if number of duplicate records exceeds number allowed
Thanks in advance for any help. I'm trying to create If DCount code on my form that will count the number of duplicate records and then notify me when the number of duplicate records exceeds the number allowed. The number of duplicates allowed is established in tblFloorProgCriteria in the field FloorProgMaxObservations. Here's what I have so far...It's giving me the message as soon as I attempt to add the first record. If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & " And [FloorProgCriteriaID] = "...

Show header in cell
I want to be able to show my header in a cell of the associated spreadsheet? Any ideas?? -- Thanks, Darrin How about turning things around and having a value in a cell (or cells) on your worksheet that then forms the header for your report. I'm not sure myself but this thread seemed to work OK for someone else .... http://www.excelforum.com/showthread.php?t=379477&highlight=cell+header Hope this helps -- tonywig ------------------------------------------------------------------------ tonywig's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18985 ...

Show actual values added instead of cell references? (=A1+A4 shows as =10+2 in cell)
Is there a way to do this via VBA or formula? I want to show the values that are added instead of the cell references. Let's say I want to Add use a cell to calculate =A1+A4...with A1=10 and A4 = 2, I want the cell result to be 12 however the formula to be =10+2 instead of =A1+A4...is this possible? Check one of your other posts. Naji wrote: > > Is there a way to do this via VBA or formula? > > I want to show the values that are added instead of the cell > references. Let's say I want to Add use a cell to calculate > =A1+A4...with A1=10 and A4 = 2, ...

Moving Cells
I have a spreadsheet with text in A1, A2, A3, A4, A5, A6, ..... and so on. I would like to have the text in each one of those fields run down the left side for example A1, B1, C1, D1, E1, F1, G1, H1, I1, etc.... Can this be done? Thanks ! Ty Select all the appropriate cells in Column A, Copy, select B1, on the Edit menu select Paste Special, Transpose. Delete Column A. HTH -- Sincerely, Michael Colvin "Ty" wrote: > I have a spreadsheet with text in A1, A2, A3, A4, A5, A6, ..... and so on. > > I would like to have the text in each one of those fields run down th...