In the attached sheet, I have measured data from a device. It shows Input, Output, gain and delta. You can see as input increases, delta comes down. I want to find the output corresponding to delta od -1. In the table, I have enteries for delta of -0.982 then -1.382, not for -1. How to find output corresponding to delta of -1?
View 6 Repliesneed an excel simple formula or function that returns values of y for given x in a two-dimensional array and works in the following way.actually a combination of linear interpolation and lookup/linear search. as in following example
x y
1 10
2 15
3 20
if x=1.5 then y=12.5 (answer available thru FORECAST but incomplete)
if x=2 then y=15 (answer available thru LOOKUP but incomplete)
so for whatever the x i need/get a valid y thru a single formula/method !
sorry m not good in math,
I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.
View 6 Replies View RelatedI need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.
The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.
GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+
payout %10.0%10.0%14.6%19.2%23.8%30.0%
I have data that consists of a set of coordinates that is indirectly related to an angular measurement. The data set that I have consists of four sets of coordinates and four coresponding angles. The data is below.
angle,43.09,34.83,26.8,18.08,
X,174.24,187.45,202.18,213.87,
Y,140.13,121.84,103.3,81.46,
How can I get excel to give me values for X and Y based on an angle that I input, such as 22.45. plotting a graph of the X and Y gives a curved output, so the methods that I know of do not seem to be working.
I have other more complex sets of data with complex curves that I would like to use a similar method to mine data.
I have a dataset of 360 columns and 180 rows in Excel 2007 and I want to increase the resolution of the dataset by 2 (i.e. I want to insert a value between each pair of cells, both vertically and horizontally). The calculation (interpolation) need not be complicated - I was thinking along the lines of a simple bilinear interpolation, where each new value is based on an average of the 4 cells surrounding it.
I'm fairly new to this type of operation in Excel, so I'd appreciate it if any responses I receive aren't too vague!
I need to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.
Attached file is the example : Matic Interpolation.xlsx
Is there a excel spreadsheet function that can be used to interpolate between two columns of data? If not, can you recommend a quick equation to perform interpolation? I tried with "IF(AND" conditions, but we can't use it for more than 7 set of values
View 4 Replies View RelatedI have a frequency table (attached) that I need to be able to interpolate values between the known values on something other than a linear basis (e.g. exponential or logarithmic).
View 5 Replies View Relatedinterpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
which function to use to find the row number of a value for interpolation.If I have a table of 5rows and 5 cols, how to find those rows of those 2 numbers between which my interpolation should be done.
X Y
2.2 3.45
2.9 4.56
3.3 6.2
3.7 7
4 8.1
Now I want to interpolate for 3.1. So how to find the row numbers of 2.9 and 3.3
Power.jpg I must write a double interpolation formula for the table.
View 6 Replies View RelatedI am looking for a VBA code which can be used to interpolate between values at a given interval and return these values to an excel sheet. I have a list with X and Y values and need to interpolate (linear) between two X points to return intermediate values for Y (interval between values is defined by the user in a userform). The code is to continue with interpolation between two consecutive X points until it has reached the end of the listing. In the example only several points are given, but the list can be longer or shorter than given in the example. The results need to be returned to a new sheet.
View 4 Replies View RelatedI am trying to create an interpolation for water flow in a given area. Tables below for better understanding:
Inputs Required
Area
3
km2
L
5
km
L2/A (Calculated)
8.3
[Code] ......
The idea is that when I increase the input area, I would like to interpolate between the RFFP frequency for the flow Q50/Q20 that best fits the area and provide me with a peak flow rate.
I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved
View 8 Replies View RelatedI would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?
I have to convert the first to the second program, but i don't know how to do this:
MAD.png
ABStolinear.xlsx: the first sheet is the first program which is correct (i think), the second sheet is a failed attempt to write the second program
I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved.
So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.
If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?
View 1 Replies View RelatedI have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
in this example below i need to work out what the divsor would be for values inbetween the data?
View 9 Replies View RelatedI have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:
z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494
My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).
Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.
I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?
View 3 Replies View RelatedI have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007
Writing a macro to run SOLVER on a linear program. I have twenty tabs, each with a linear program and a solver already set up (constraints, cells to change, and cell to maximize). I would like to run a sub that will solve each tab's LP by running the tab's SOLVER...
If it makes it easier, my cell to maximize is I17, by changing B16:H16, subject to I20:I40 SolverAdd -> SolverOk -> SolverSolve
I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.
View 3 Replies View RelatedI would like to find the intersection points of all of the linesshown on the included chart. If that could be output into a table, by the day, that would be ultimately what I am after. http://bladeseri.50megs.com/Intersects.png. Guess 50megs.com doesn't allow this type of reference. Here is Geocities link http://www.geocities.com/bladex2007/Intersects.png
View 5 Replies View RelatedForecasting using historical seasonally effected data
I organize my thoughts in numbers so it will be easier to understand.
[1] Say I have this data from January 2009 to December 2009:
1-4-7-10-13-14-15-16-18-20-22-25
As you can see, the growth is not linear.
[2] So then, I start with this data point for Janaury 2010.
26-a-b-c-d-f-g-h-i-j-k-l
[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).
[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:
26-a-b-c-d-f-g-h-i-j-k-50
So how do i do this now?
I am looking to find out certain values from a current set of data using linear extrapolation in Excel 2010.
I have attached the data i am using below:
Data.png
I need to find out the specific power output and heart rate values, at a set blood lactate value (i.e. When the blood lactate value is exactly 2.0mmol.L-1, what is the given power output and heart rate).
I have tried plotting the data into a line graph with a secondary axis, however because i am unable to change the horizontal axis range for the power output values, i cannot interpret the data in this way.
I have 32 pairs of coordinates (X,Y). If I graph them using a scatter chart in Excel, I can see that it looks like they suggest a linear relationship between the points.
Other than printing the chart and trying to draw a "best fit" line manually, is there some sort of way I can get Excel to come up with the "best" linear formula (y=mx+b format)?