#### Most logical value

```I would like to know if there is a method of determining "the most
logical" value in a table.
These are some data that I put in the table below. These numbers are
obtained from some dedicated software to compute the influence of an
investment on some projects. I would like to fill in the gaps myself.
I can add the data from column 10 en 20 and divide it by 2 to compute
the value for column 15, but is there a better method?

investment
year	0	5	10	15	20	25	30
2005	72,8	72,8	72,8	72,8	72,8	72,8	72,8
2006	79,4	76,7	71,5		60,9		50,3
2007	92,3	87,4	76,9		55,6		33,5
2008	108,6	102,3	86,2		53,3		19,0

Thanks,

Alex
```
 0
alex5584 (2)
2/13/2005 2:57:51 PM
excel 39879 articles. 2 followers.

2 Replies
399 Views

Similar Articles

[PageSpeed] 14

```That assumes a linear model.  You can use linest to develop the coeficients
for a non-linear model.

You can see how Bernard Liengme solves for the coefficients of a polynomial
curve using LINEST.

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

--
Regards,
Tom Ogilvy

"Alex" <alex@nowhere.com> wrote in message
news:uZLAmxdEFHA.392@TK2MSFTNGP14.phx.gbl...
> I would like to know if there is a method of determining "the most
> logical" value in a table.
> These are some data that I put in the table below. These numbers are
> obtained from some dedicated software to compute the influence of an
> investment on some projects. I would like to fill in the gaps myself.
> I can add the data from column 10 en 20 and divide it by 2 to compute
> the value for column 15, but is there a better method?
>
> investment
> year 0 5 10 15 20 25 30
> 2005 72,8 72,8 72,8 72,8 72,8 72,8 72,8
> 2006 79,4 76,7 71,5 60,9 50,3
> 2007 92,3 87,4 76,9 55,6 33,5
> 2008 108,6 102,3 86,2 53,3 19,0
>
> Thanks,
>
> Alex

```
 0
twogilvy (1078)
2/13/2005 4:51:23 PM
```Tom Ogilvy wrote:
> That assumes a linear model.  You can use linest to develop the coeficients
> for a non-linear model.
>
> You can see how Bernard Liengme solves for the coefficients of a polynomial
> curve using LINEST.
>
> http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm
>
>
Thank you!
In my model it should be sufficient to use TREND. I found it when I was
looking at LINEST ;-)

Alex
```
 0
alex5584 (2)
2/13/2005 9:39:47 PM

Similar Artilces:

pasting of variable cell value into macro
I have written a macro that runs an autofilter which grabs cell info from a different worksheet within that workbook (an entered date). It then uses that date to autofilter. It works fine the first time, but aparently plugs that info permanently into the macro and will not work on subsequent runs. I need a macro that will grab variable info from a certain cell each time and run with that new cell value each time. Any ideas?. (& yes, I am a newbee) sorry.. Your problem is refreshing the autofilter. It is best to clear the old filter before applying the new filter. Here ...

Value Y axis label is cut off
I can't figure out how to increase the "text box" around the Y axis label. In the display, it shows the entire word, but in the actual chart, the last letter of the word is cut off. It appears that I can only move the position of the entire label, but I'm not able to stretch the label so that it includes the entire text. I tried putting a couple spaces after the text in the Title field in the Chart Options screen, but this didn't help. Example: The Y axis label is "Annual Return" and it looks like it says "Annual Returr" due to the "n" b...

Viewing field values at the accounts level
I created several customised fields and bulk imported account data - all looks fine except for one numerical field, which does displays only blanks. When you double-click on the account, however, you clearly see there is a non-zero value there. When I sort by this field, it does the right thing, just that somehow the field value does not show up under the field name at all when viewing all accounts. Any help would be great. Textbooks etc don't seem to cover this sort of newbie snag! Thanks ...

Lookup value off by one row
Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!\$A\$1:\$A\$402,LookUpValues!\$B\$1:\$B\$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Assign a Value to a Duplicate Entry Within the Same Column
Within a single worksheet I have thousand-plus rows of data and one of the columns within that worksheet I have a series of numbers. After I've identified the duplicates within that column I want to assign a value based on the first time that number shows up (in this case by I'v sorted by date), so the first duplicate would have a value like "1." I then want to assign the next duplicate(s) sequentially (2, 3, 4, etc.) When finished I would want a new column of data that would have far more "1s" than "2s," more "2s" than "3s,"...

Help with interpolating values
Hi, I have two series of data as follows: Distance Height 0 0 6 12.5 11 23 12 16.25 14 26.75 24 27.25 42 22.75 45 30 55 52 70 67 90 79 115 83.5 I need to find the heights corresponding to : 5. 10 15 20 25 30 and so on till 115 (ie at distances in multiples of 5) What is the best way to do this? I am new to statistical functions in excel. Thanks in advance for the help. Regards, Raj On Tue, 6 Apr 2010 03:22:50 -0700 (PDT), Raj <rspai9@gmail.com> wrote: >Hi, > >I have two series of data as follows: > >Distance Height >0 0...

Importing values w/trailing minus signs
XL97 & 2k For many years I've regularly imported downloaded (mainframe) files into XL and, where there were values with trailing minus signs, I used the Import Wizard to define the position as a separate column. I wrote a utility macro that flips the value. Recently someone mentioned that they accomplish this with the import wizard and I looked until I was blue in the face but cannot find it so I assume that-if this individual was correct-it is in a version newer than the one(s) I use (I didn't hear what version he was using). Is this correct, or am I indeed blind? -- Regards; R...

Catch when the Probability value has chaged due to a Sales Process
Hi I'm coding a callout for the CRM 3.0, I would like to be able to catch when the opportunity's Probability value has change due to a stage change in a Sales Process workflow. I have the following code: public override void PostUpdate( CalloutUserContext userContext, CalloutEntityContext entityContext, string preImageEntityXml, string postImageEntityXml) { string preProbability = String.Empty; string postProbability = String.Empty; // Get pre-values XmlDocument preImageEntity = new XmlDocument(); preImageEntity.LoadXml(preImageEntityXml); foreach (XmlElement elemen...

Formula to sum values extracted from string
Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Mode function
Hi, I'm using the following expression to return the mode of a list of numbers: =IF(H1028="","",MODE(H1028:H1031)) If the four checked cells all have values but there's no two values the same (i.e. no mode) what I need is an expression that will return me a default value of 2 rather than the #N/A error value. Thanks for looking, Steve. =IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31)) Regards, Stefi „Struggling in Sheffield” ezt írta: > Hi, > I'm using the following expression to return the mode of a list of numbers: > > =IF(H1028="...

Percent value rounds up in Access
I have a table with a field that is set to Number, Double, format percent, 3 decimals THere is a form through which the value is entered into the table. Its format is set to Percent and 3 decimals When the value is entered into that field, it displays 3 decimals of a percent - but the actual value is rounded to the second decimal point. How can I get to display the Percents WITH 3 decimals without rounding? Thanks, When I go to the Format tab of the properties sheet for the control that displays the value and set the Format to Percent and the Decimal Places to 3, I get a percent with three d...

How do I search for the second largest value in the array?
How do I search for the second (or third and so on) largest value in the array either in column or row? =LARGE(A:A,2) =LARGE(A:A,3) =LARGE(1:1,2) =LARGE(1:1,3) -- David Biddulph "Golf" <Golf@discussions.microsoft.com> wrote in message news:241155EE-30CA-4E7C-8E8C-2215F1523919@microsoft.com... > How do I search for the second (or third and so on) largest value in the > array either in column or row? Assume that you would like to get the result from A column Data. Column:- This will get the Largest number from A Column =LARGE(A:A,1) Similar ...

Hiding zero values
I have a chart with data in Columns C and E. I want the columns to zero out when subtracted (ex:=E2-C2). My issue is I only want zeros in the answer column G to display in the rows with data, not in the rest of the column where I put the formula. Is that possible? Any ideas would be helpful. I can't hide zero values becuase then nothing in the column would display at all. I tried an If Statement but I get a circular reasoning error. HELP! Submitted via EggHeadCafe - Software Developer Portal of Choice A C# WaveOut API Player - Recorder Library http://www.eggheadcafe.com/tutorials/...

Possible to hide a sub-report if NULL values?
Hi, I have created a report (column) that list open invoices and I have also attached a sub-report (different table) that displayes comments. Problem: sub-report is showing for all invoices, but I would like it to show ONLY for invoices that has a comment. That is, invoices that has no comments only list their usual values, and invoices with comments I have a sub-report displaying the comment. Is that possible? (otherwise my invoice-list with contain a lot of unecessary space - used by the sub-report). Kindly, Mikael Mikael Lindqvist wrote: >I have created a report (column) that l...

Icon Sets
I would like to display in cell A2 a red diamond if the value in U2="Storage"; a Green Circle if the value in U2="Central Files" "Office" or Floor"; and a yellow triangle if the value in U2="Missing". I saw the response to "ICON Arrows UP or DOWN", but I couldn't understand where the different parts were supposed to go. Nolene, you can put an IF statement in column A that reflects the information in column U: =IF(U2="Storage", 1,IF(or(U2="Central File",U2="Office",U2="Floor"),2,...

How do I chart two separate sets of x and corresponding y values .
I would like to chart two sets of x and y values with each set of x values corresponding to its own set of dependant y values. The two sets of x values for the ordinate are entirely different. It seems like I can only chart two sets of data on the same graph with the same values for x and two separate sets of dependant values for y. Is there any way possible to get around this problem? Select your first set of X&Y data, Insert/ Chart (Chart type XY, & choose an appropriate sub-type)/ other options as appropriate. Now select your second set of X&Y data, Copy, select your c...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

How do I change FALSE values to 0?
How do change values of TRUE or FALSE to 0? what is the formula you are using? "Redleg40" wrote: > How do change values of TRUE or FALSE to 0? =IF(AD7<0,AD7*-1) & =IF(AD13>=0,AD13) "Jambruins" wrote: > what is the formula you are using? > > > "Redleg40" wrote: > > > How do change values of TRUE or FALSE to 0? Put a 0 in for the value_if_false argument. Standard IF syntax =IF(condition,value_if_true,value_if_false), so for your first formula =IF(AD7<0,AD7*-1,0 -- MrShort ----------------------------------------...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

Find the top value in a list
I have a worksheet created in EXCEL 2003 which i use to record swimmers times in several lists. One examlpe is in cells D109 to 113. In cells D111 to 113 are recent times and D109 to 110 are empty. As I add current times to the top of the list. I want to be able to retrive the current value at the top of the list and show it in D106. D107 & 108 are used for other values. At present I have the following formula in cell D106 =OFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0). This works well but makes updating the list difficult. Is there a simpler way to achive the same...

How do I run a macro when a value occurs in a cell
I want to run a macro when a value appears in a cell. How can this be accomplished? Hi! This is a trivial example but you can build on it: Private Sub Worksheet_Change(ByVal Target As Range) If Range("D3") = 36 Then Range("D3").Interior.ColorIndex = 6 Else Range("D3").Interior.ColorIndex = 2 End If End Sub Put =A3*3 in D3 Try values such as 12, 10 in A3 Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforu...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!\$F\$7:\$F\$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!\$E\$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

Tools/Options/View/Zero Value
Under Tools/Options/... I set up the defaul drive in G (net work drive). Today, the network drive is down and I am working on C drive. I'd like the worksheet doesn't show zero values when viewing it, so I unchecked the box "zero values". But it keeps giving me the message "Cannot access directory G:\excel\...", and the zero values are still there. How can I by pass this message and the zero values would not show? Thanks. Mia ...

Lookup / Array Range / find the value
I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission \$0 \$20,000 \$1,000.00 \$...

Hided values appears when I'm copying/pasting it to other worksheet
So, in one worksheet I have some data and there is some colums hided. When I selecting all that data and pasting to other worksheet, al these hided colums or rows values appears and I have to hide it again. Is there possibility to copy/paste in such way, that these hided value don't appear again? Thanks in advance Best Regards, Aivara -- Aivaras_Bakana ----------------------------------------------------------------------- Aivaras_Bakanas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2518 View this thread: http://www.excelforum.com/showthread.php?threa...