# Extracting Variables From Quartic Function (4th Degree Polynomial)

Apr 10, 2012

I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW(\$1:\$10000))))

but it only extracts the first variable.

4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e

Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4

I'll like to have the final result like below (Based on the Example equation above)

edcba-42.4276319.989-809.094942.247-397.587

## 3rd Degree Polynomial

Jul 31, 2008

I have this data:

x y
310 516
320 526
325 535
330 587
335 640
340 715
345 857
350 1040

When I perform a third degree polynomial curve fit I get this equation:
y = 0.0106x3 - 10.0266x2 + 3152.3381x - 329943.8370
R2 = 0.9987

Which isnt right. On a calculator, I get negative numbers. Does excel have problems with 3rd degree polynomials?

## Calculate 2nd Degree Polynomial Trendline Coefficients In VBA Without Using Cells

Jul 17, 2014

i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:

VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}

i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):

cp25 = 560
cp26 = 570
cp27 = 580
cq25 = .414
cq26 = .479
cq27 = .536

1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells
2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?

All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.

I have tried

VB:

Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1)
[COLOR=#333333][/COLOR]

but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).

## Polynomial Function

Feb 9, 2010

I have plotted a graph which compares the miles done with the petrol used.

I'm trying find a formula which allows me to apply the petrol and would give me the miles covered.

From excel I have applied the tread line of polynomial and obtained the following formula...

y = 1E-06x5 - 0.0002x4 + 0.0161x3 - 0.4893x2 + 7.5295x - 20.87

Rē = 0.9989

When is it applied to excel with the value of petrol it does not give me the miles.
I'm working out the formula in a incorrect manner?

I have attached the excel file.

## VBA Array Function For Polynomial Coefficients

Mar 9, 2007

I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula:

{POLFIT(Xa, Ya, N)}

Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit.

It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells.

I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is

Function POLFIT(Xa, Ya, N As Integer) As Variant

Various means I have tried to count POLFIT do not return the correct value.

## Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006

Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

Feb 16, 2009

I'm logging temperatures and I want to add the degree symbol "°" to the end of the numbers, how do I do that? It's easy if I just use one number (#°), but how do I do it when adding the lowest and highest in the same cell, e.g. 33-35?

## Delete Degree Symbol Through VBA

May 22, 2014

Have written all the code to remove various words,numbers, etc from cells but this one symbol...I can not figure it out.

It looks like this:

Its the degree symbol, however using that symbol in VBA has proved to fail me. I have a large data set that is created for import but it will continue to fail until I remove this, Find(Replace) should work right? But using a search in VBA it deletes the entire cell.

## Replace Decimal With A Degree

Aug 15, 2007

I have a columb of positions which at the moment have a decimal place, I want to substitute the first decimal with a degree ie. S27.01.050 for S27°01.050 This has to apply to all cells in that columb.

## IF Function With Three Variables

Feb 14, 2013

I'm having trouble with my logic again :/

AREA
Tp std
Tp

30
5

So I want the Tp in the 3rd column to show the result:

IF AREA is less than or equal to 20, Tp=Tp std / 2

IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)

IF AREA is 40 or greater Tp = Tp std

But as one equation

I have just been struggling with the range part.

## Polynomial Equations

May 14, 2006

y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4

Above is the polynomial equation of a trend line as shown on my chart, I have extracted the numbers into separate cells using LINEST, so now I have in cells A20 to E20 the numbers 1.083333333, -7.333333, 16.416667, -6.16667 and 4 respectively.With a "y" value of 25 how do I go about solving for a value of "x".

I've been stubbornly struggling with this on and off for a couple of weeks now and I'm not getting any closer to working it out and the websites I've found explaining the procedure all seem to go over my head by the time I get into the fourth or fifth paragraph.

## INDIRECT FUNCTION With VARIABLES

Feb 14, 2007

I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....

I have this formula: but it does not work

I'm not sure how to include the VLOOKUP function in my argument to

## Convert Degree Minutes And Second Lat And Long To Decimal

Jun 12, 2013

I'm trying to convert degree minutes and second latitude and longitude to decimal lat and long in excel (I have 100s of such conversion to undertake). When trying out dividing the minutes and second by 60 and checking my calculation on Google Maps lat/long finder or Latlong.net my calculations are not correct. However the equation to other way around - converting from decimal lat long to DMS (*60) works.

## How Do I Calculate A Polynomial Curve Fit

Sep 7, 2009

For Excel 2007 -

I am looking for a function [or method] to calculate a polynomial curve fit. I can do it on a graph with the existing graph and chart tools, but I need to calculate it myself. Does such software exist somewhere in my Excel 2007 package, or can anyone recommend a freeware program? I am hesitant to download an unknown, un-recommended add-in.

## More Digits From Polynomial Regressions?

Nov 21, 2008

The functions of trend lines in charts are nice, but the coefficients of the output equations are all with only one-digit for the polynomial fits. The single-digit fits by the fitting equations often result in higher deviations from what we have seen in the charts.

I also tried to use the statistical functions but it seams that I cannot get any outputs of these coefficients except for the slope of LINEST, which is not sufficient for polynomials.

I also use addins such as as regressions but cannot get anything either to help for these coefficients.

May someone tell me how to make such outputs with more digits so the equations can be more precisely fitting please?

## Solving Polynomial Equations

Feb 16, 2007

I don't actually own Excel as yet - but I'm wondering if someone could tell me whether Excel is capable of solving what I'm told are 'polynomial equations'. An example of the sort of equation I hope to solve is: d/(1.18 + d) + d/(1.86 + d) + d/(9 + d) + d/(8.8 + d) = 2
d=? {I happen to know d = 3.65(approx) in this example btw}

Often there will be many more than four terms...

## VLookup Function That Searches For Two Variables?

Dec 9, 2013

I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;

vlookup -- 12253 & 605.34 (if these are matching then) 131001

## Range Variables In Customized Function

Aug 30, 2006

I wrote my function as it is shown below:

Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function

This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})

However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............

## Nested If Formula/Function With Other Variables

May 14, 2008

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

## Extracting Consecutive Letters From Cell And Applying IF Function

Mar 8, 2013

I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE

If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

## Formula Is To Work Out The Class Of An Honours Degree For A Certain Student

Mar 4, 2008

=IF(H29=>70,"1st",IF(H29>=60AND>=69,"2:1",IF(H29>=50AND>=59,"2:2",IF(H29>=40AND<=49,"3rd"))))

The formula is to work out the class of an honours degree for a certain student.

## Create A Polynomial Equation Of Z From The X And Y Inputs?

Jan 20, 2014

I'm familiar with 2d graphs, trendlines, and regression equations. Now I have some tabular data that has 2 input variables and a result. As with my 2d data, I would like Excel to create a polynomial equation of z from the x and y inputs. Can Excel do this, or do I need a plug-in or another software package?

## Multivariate Polynomial Regression In Excel?

Feb 8, 2010

I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:

Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3

Using the following command, I have achieved this:

=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)

theory Excel uses for this kind of regression?

## Defining A Range With Variables And Cells Function

Aug 25, 2009

I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.

## Insert Relative Function That Uses Variables Into Cell

Mar 4, 2008

I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...

I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....

## Restricting Vlookup Function For Extracting Data To Single Item?

Mar 16, 2014

Currently I am facing a problem that I want to copy data from sheet 2 to sheet 1.Here I am using vlookup function.But I have a problem that when there is two items of same name in sheet 1,it copies data from sheet 2 into both same items in sheet 1.How can I restrict and copy data to only 1 items.The sample data is as shown below :

Sheet 1 (Including duplicate item)

Apple
Apple
Ball
Cat
Cat

In sheet 2 (Unique item)

Apple 25
Ball 36
Cat 34

## VBA Match Function With Variables / Number And Alternating Ranges

Jun 2, 2014

I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.

ExampleFile.xlsm

## Index Match Function For Multiple Linked Variables

Nov 21, 2005

I have a worksheet that has 6 columns of numerical data that all column
datasets are referenced together for each row. So for example the data
in row 5 in all colums is related.

Column C through F have 4 numbers that refer to the data in the first 2
columns. These are also ordered by row.

I need to look in column A for 0.00, and column B for 3.14 Both of
these must be in the same row or I need the closest value. When these
values are found in columns A & B, return the values from the same row
in Columns C through F.

## Add Nest Formula/Function To Cell With Variables Via Macro Code

May 22, 2008

I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F\$2,F\$3,F\$4,F\$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

## Extracting Data From File In Directory And Extracting Filename

Mar 20, 2014

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.