Calculating values for empty cells.

Hello.

I have a very simple problem that I cannot find the answer to. I have data
in two columns, some of the data in one of the columns is missing and I want
to automatically extrapolate what the data should be based on the trend. How
can I get Excel to fill in empty values without overwriting the known
values. Below is a sample of my data.

     1500
     1600
     1700
     1800
      4000 1887
      5700 1900
      5500 1910
      7300 1912
      8100 1920
      8800 1926
      10100 1930
      11900 1936
      12200 1938

-- 
Ryan Taylor
rtaylor@stgeorgeconsulting.com


0
rtaylor (2)
12/22/2003 7:04:56 PM
excel 39879 articles. 2 followers. Follow

1 Replies
689 Views

Similar Articles

[PageSpeed] 27

Not sure what you mean by "automatically", here, but you can 
extrapolate the values using this array-entered formula 
(CTRL-SHIFT-ENTER or CMD-RETURN):

Assume data in A1:B13. Select B1:B4 and array-enter
   
=TREND(B5:B13,A5:A13,A1:A4)

If the series is an exponential growth series, use GROWTH() instead.

In article <##Ol#5LyDHA.2064@TK2MSFTNGP10.phx.gbl>,
 "Ryan Taylor" <rtaylor@stgeorgeconsulting.com> wrote:

> Hello.
> 
> I have a very simple problem that I cannot find the answer to. I have data
> in two columns, some of the data in one of the columns is missing and I want
> to automatically extrapolate what the data should be based on the trend. How
> can I get Excel to fill in empty values without overwriting the known
> values. Below is a sample of my data.
> 
>      1500
>      1600
>      1700
>      1800
>       4000 1887
>       5700 1900
>       5500 1910
>       7300 1912
>       8100 1920
>       8800 1926
>       10100 1930
>       11900 1936
>       12200 1938
0
jemcgimpsey (6723)
12/22/2003 7:51:23 PM
Reply:

Similar Artilces:

Duplicating a workbook only with cell's values
Hello every body, I have a complex workbook. It it is becaming a little bit difficul to work into it. It is possible to do a copy of this workbook but thi copy should have only cell�s values not cell�s formulas. As I said thi workbook is complex to replecate sheet by sheet to another workbook an do the paste especial process. thank you in advanc -- Message posted from http://www.ExcelForum.com Hi Adrix There is no option to save it like this Try this macro on a copy of your workbook Sub test() Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).S...

Keeping the Same Cell References
What's the easiest way of copying a cell with a formula in it and pasting it into another cell with the exact same formula? I also have ASAP utlities, and I couldn't figure out how to do it with that either. One way: Remove the = sign before copying and re-insert it in the new location after pasting -- Kind regards, Niek Otten Microsoft MVP - Excel "JP" <JohnP26@msn.com> wrote in message news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com... | What's the easiest way of copying a cell with a formula in it and | pasting it into another cell with the exact same for...

Changing Names of Multiple Cell Link Ranges
I have a series of drop down boxes created with the Forms menu. The cell link range for each dropdown is different, as follows: Cell Link Box1 = DemandBase_A_UndistExp1 Cell Link Box2 = DemandBase_A_UndistExp2 Cell Link Box3 = DemandBase_A_UndistExp3 Cell Link Box...n = DemandBase_A_UndistExp...n I want to change them all at once to: DemandBase_A_OtherCost1 DemandBase_A_OtherCost2 DemandBase_A_OtherCost... I know I can change the entire name using the following code: Sub Change_Drop_Link() For Each bx In ActiveSheet.DropDowns If bx.LinkedCell = "DemandBase_A_UndistExp1" Then ...

Cell Reference Formula Outside Workbook
I am trying to reference a cell outside of my current spread sheet. I am using a cell in my current sheet as an input for the extension of the file I would like to look in. So I have this defined as LINK. And the value of link is something like C:\\Myfolder\ready.xls I am trying to set up a formula in my sheet that will use this reference to the cell on the first page, which I defined as LINK so when I change the value of the cell all my values will update to the new folder and document, but retain their previous page, column and row references. For example. My current formula reads [...

Re: Find last cell in a column, Delete its contents and make it ac
Sub uselastcellinL() Application.Goto Cells(Rows.Count, "L").End(xlUp) ActiveCell.ClearContents End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Don Guillett" <dguillett1@gmail.com> wrote in message news:... > Sub deletelastcellinL() > Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "George" <George@discussions.microsoft.com> wrote in message > news:D...

Newsgroups From columns empty.
All of the "from" columns in my newsgroups are empty, I cannot see who posted. The headers in the preview and reading panes are fine though. How can I get the from column back to normal? Thanks. ...

0 values further issue
Hi Guys I have used =IF(Original!C33="","",Original!C33) to avoid pulling 0 numbers across but I have come across a secondary problem where =IF(A33>0,VLOOKUP(A33,'New Labour Standards'!$B$6:$C$990,2,FALSE)," ") and other formula on the same row beggining with =IF(A33>0 are now displaying #n/a entries. I want these entries blank if a33 is blank Thanks Brian Change them to: =IF(OR(A33="",A33<=0),"",VLOOKUP(A33,'New Labour Standards'!$B$6:$C $990,2,FALSE)) Note that I have made it "" rather than " &q...

count cell with background color 36
First I want to count the number of cells in a range that have text in them. Then I want to count how many of these cells have the back ground color 36 Oldjay This will achieve what you want for a continuous range, Column A in this example. Change the variables to suit. Take care Marcus Option Explicit Sub MyCount() Dim lw As Integer Dim counter As Integer Dim MyConstant As Integer Dim i As Integer lw = Range("A" & Rows.Count).End(xlUp).Row counter = 0 MyConstant = Range("A2" & lw).SpecialCells(xlCellTypeConstants).Count MsgBox "The...

Ooh .. Linking a list to a list to an output cell
I have a single cell in which i enter the price of a home. Elsewhere, I have two columns of ten items each: one column is formulated to give relative prices based on the price entered in the single cell, and the column adjacent to this column describes these numbers in nominal terms (i.e. ten items in increments of five percent: "25% more" .. " 0% change" .. "25% less"). Further along, I have a list box created which uses the nominal column at the Source for the list. I would like to have a cell adjacent to the list box cell which will give the price b...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

Incrementing a Field to the Next Value
Hello: I need a T-SQL query that returns a string value and, from the latest dataset that I have, returns the next number. This would be, then, that string value "+1". Now, regardless of whether there are leading 0's, I want SQL to return the next number for this string field. This, in essence, is the value returned from the query "+ 1". So, if the value in the field were 000039, then I would want SQL through this T-SQL query to return "40". Or, if the field were 1157, I would want it to return "1158”. In other words, I want for...

calculating #2
I have an EXCEL workbook that for some reason does not calculate completely. when i hit F9 for it to calculate it takes a long time and at the bottom of the page the word "Calculate" does not go away. any ideas? Hi Frank, Calculate is an indication that you have turned Calculation off in Tools, options, Calculation (Tab), [X] Manual -- and that you have something that needs to be calculated. If that is not the problem then try Ctrl+Alt+F9 which recalculates all cells on all worksheets in all open workbooks. If you still have a problem show us what your formulas loo...

Replacing values based on a set list
Hi Could anyone help me with this one: - I have an Excel workbook with two worksheets: sheet1 and sheet2. On sheet1, I have 2 columns of data:- Column 1 - CityID Column2 - CityName On sheet2, I have a list of 500 office locations, some of which are in the same city. Sheet2 has several columns, one of which is the city location of the office. I am trying to prepare the Excel sheet ready to import into Access and want to replace each instance of the city location listed on sheet2 with the CityID number from sheet1 where the city is matched. For example: - Sheet 1 CityID CityName 1...

Repeated Values in a Spread sheet
Hi.. I have a captured csv file of a telephone bill. I would like to filter the data as follows, The numbers called must be grouped together. the most dialled numbers must be at the top (or bottom) of the sheet- maybe indicating the number of times it was dialed (occurance) Thanx -- !ngeniuos (99% Perspiration) You should obtain a unique list of numbers dialled on a separate sheet using Advanced Filter, then you can use a COUNTIF formula to count how many calls are made to each of those numbers, then sort this in descending order. Hope this helps. Pete On May 23, 9:40 am, !ngeniuos &...

Locking cells #7
Is it possible to lock the cells with conditio. I want to lock all the cells after one week from the certain date on the sheet. for Ex. $A$23 cell have the date 15 Aug 2005 and i want to lock the cell $C$8 to $H$23 after one week from that date. so that no one can change the data. Thanks in advance By default all cells are locked. You would need to unlock all desired and then have a macro to lock the desired cells. Sub lockcells() Range("b1:d12").Locked = False If Date > Range("a4") + 15 Then Range("b1:b4").Locked = True End Sub -- Don Guillett SalesA...

Data Migration
I'm trying to migrate Sales History information into CRM. Invoices and Invoice Lines are migrating and calculating the Extended value correctly. I'm having a problem with Credit Notes. Credit Note Lines (where Price per unit < 0) are not migrating. I'm getting a "CrmIncidentUnknownError" in the migration log for these lines. The same error occurs for lines where Price per Unit = 0. I've modified all the relevant value fields on the forms to allow minimum values of -100,000,000. I'm using Existing products and over-riding the Price each time. I previously ...

Can't get a value from a querry into a forms text box
I have a main Shippin Control table, and a main Shipping Control form with description of cargo and quantity text boxes. I have a querry which is a join of tblValues and tblShipping_Control. The SQL is below SELECT Shipping_Control.DescriptionofCargo, Shipping_Control.CargoQty, tblValues.Value, ([Shipping_Control].[CargoQty]*[tblValues].[Value]) AS CommercialValue, Shipping_Control.ShippingDate, Shipping_Control.ShipTo, Shipping_Control.ShipFrom, Shipping_Control.CollectionNoteNo, Shipping_Control.PackingListNo, Shipping_Control.ShipmentID, Shipping_Control.Customer FROM Ship...

reference to lookup value
I'm trying to write an expression that refers to a value in a combobox on a form that is derived from a lookup field on a table that (of course) refers to values on another table. How can I properly reference this? Messing around with the bound column properties for the combo box and the underlying table has not helped. The only values I can get passed through to the VBA editor are the index value and null.-- Why are you asking me? I dont know what Im doing!Jaybird "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message news:0D3D41C1-3C87-471F-983E-E2285D61E8CA...

Automatic Mortgage Calculation
I am running MYM2003 SE Version 11. I have set up my mortgage account as a loan with all of the information regarding rate, term, amount, etc. I pay my mortgage to the bank from my checking account online. When I prepare the transaction, I input the Category as Loan Payment:Mortgage ( name of mortgage account). The mortgage account collects the data associated with the transaction but incorrectly calculations the amount of interest vs. principal. In fact the calculation is also inconsistent. (i.e. January Payment Principle 700 vs. 1243.69, March Payment Principle (138.67). This requires m...

stop rounding in cell
i want the numbers in my cells to show as they are without being rounded but to show in thousands. I want to maintain integrity. Eg. 97,654 should just show as 97. i don't want it to round up or down. is this possible? Hi Try =INT(A1/1000) but note anything less than 1000 will show as 0. Regards Roger Govier Lady112017 wrote: > i want the numbers in my cells to show as they are without being rounded but > to show in thousands. I want to maintain integrity. Eg. 97,654 should just > show as 97. i don't want it to round up or down. is this possible? Try this formula: ...

Calculating diff between date/ time only between certain hours 03-01-10
I am trying to find the diff in hours only between 8:00 and 17:00 Monday to Friday only, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! 2/19/2010 16:00 2/22/2010 9:00 ??? Thanks for any assistance you can offer! Hi there GMv1, Assuming "2/19/2010 16:00" is in cell A1 and "2/22/2010 9:00" is in cell B1. The formulae in cell C1 would be as follows : =INT((B1-A1)*24) Let us know if this helps yo...

view cell content in header
Hi folks Can I customise the header so the left section = the content of a cell in the sheet eg A1. All I seem to be able to do is add date, page etc etc.. thanks john Images of home (NZ) http://www.titahi-bay.co.nz/home What we are up to in the UK http://www.titahi-bay.co.nz You need some VBA code to do this: You can use the workbook_beforeprint event to modify the header. Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) With Me.Worksheets("sheet1") .PageSetup.LeftHeader = .Range("a1").Text End With End Sub This goes behind the ThisW...

Click Entering Absolute Cell References into a Formula
When one is in the process entering a formula into an EXCEL spreadsheet it is possible to click on another cell to automatically have this cell reference placed into the formula. However this entry always is entered as a RELATIVE cell reference. Is there a way to have EXCEL insert an ABSOLUTE cell reference into the formula?? It seems to me that it would be so common and yet I have never been able to figure this one out. Please Help Michael Karas Hi Michael! Is this what you want? Click the cell and then press F4. F4 acts as a toggle going through the four reference options. -- ...

Change default value of From: field
Hey all, Is there a registry hack or something similar that can make my default 'from' address something other than my primary address? I can easily type the alternate address in for every email but that's a huge pain. Any help is much appreciated. What type of email account? If IMAP or POP3, no hack needed - change it on the Account settings dialog. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.o...

Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.
In debugging a program, I isolated the problem to the following issue. I manually created the following situation in column A, rows 1 to 6, by copying and pasting actual data in order to accurately re-create the problem: ABN/ACN/BN * ABN/ACN/BN 3158816 40000545415/005 Excel "Help" has this to say about the asterisk: " * (asterisk) Any number of characters in the same position as the asterisk For example, *east finds "Northeast" and "Southeast" " I am assuming that the asterisk, being the wild card symbol, being the "cell not empty" symbol, ...