Obtain An Interpolate Value For Z, Given A Value For X And A Value For Y
Mar 29, 2009
I just have a quick question with regards to two-way interpolation of data.
I have a data table which has x-values, y-values and z-values.
I need to obtain an interpolate value for z, given a value for x and a value for y.
The x value specified is 1.569 and the y value specified is 1.66.
I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.
View 8 Replies
ADVERTISEMENT
Dec 7, 2009
I have data essentially comprising a set of points with x and y coordinates. Is there a way to index and interpolate between points?
I can use INDEX or LOOKUP to look up a y value fo a valid x value but don't know a way to interpolate if the value is between x values. Is this possible?
View 9 Replies
View Related
Jun 8, 2007
I got a table of two columns (A1:B6). One column (A1:A6) got temperature values and the other column (B1:B6) got enthalpy values as a function of temperature values as shown in the attached file. All values in the table are empirical values and I am trying to use them to obtain the enthaly (B11:B21) for my own temperatures (A11:A21).
I can use VLookup formula to do so but it won't be accurate because the enthalpy values are very sensitive to temperature changes. I need to use table A1:B6 and interpolate for temperature values that are located in between the integer temperature valuse. e.g. if the temperature=1.5 (between 1 and 2 in the table), how can I obtain the enthalpy value=15 (between 10 and 20) using the table.
View 9 Replies
View Related
Oct 30, 2009
i have a spreadsheet with a column of numbers in increments of 5 ie 25, 30, 35, 40, 45, 50, etc. and an adjacent column of decending numbers ie 275,273,262,250,238 where the numbers in column b decrease by varying amounts each time. for a given value say 37 i want to interpolate between the corisponding values of 35 and 40 to give an answer.
View 9 Replies
View Related
Jul 13, 2009
i'm trying to write a sheet that records weight from a date and user input
trouble is people are likely to miss days out and i want to fill in the missing dates weight based on actual inputs from other days. if i make it easier and say it like this column A is the weight, column b is the number of days gone
as an example i've made it straight forward but left 4 values out
A1616B161
A1515B152
A14B143
A1313B134
A1212B125
A1111B116
A10B107.....................
View 4 Replies
View Related
Jan 23, 2009
a formula that would interpolate the volume in the below worksheet....
View 9 Replies
View Related
Aug 22, 2008
I have an array of data like below:
1
3
5
7
9
I need that array interpolated to say 20 data points but the first and 20th data point must be the actual first and last data point of the array (1 & 9 in the example). I have downloaded the xlfun software but the interpolat function only does 1 point at a time...than as you drag the function down it changes the initial array. Of course I realy have anywhere from 40-60 data points to be interpolated to 100.
View 3 Replies
View Related
Feb 14, 2007
With a known X and Y, trying to solve for Z from a table. ie: IF a variable X is defined across a row, another variable Y defined down a column, the data field Z fills in-between. What do I need to use to interpolate for both X and Y to solve for Z?
View 9 Replies
View Related
Sep 7, 2007
I am looking to write excel function that would be able to interpolate between two list of values. One list containing xValues and another yValues and a x value the specified for which the appropriate y value is to be returned. For the moment I have written a short function interPLinear into which I wish to feed an array and then simply add those together. This is so that I can make sure that my syntax is correct. Unfortunately I am struggling with that at the moment. My function looks like this.
Function InterPLinear(xValues() As Double)
Dim Sum As Double
InterPLinear = 0
InterPLinear = InterPLinear + xValues(1)
InterPLinear = InterPLinear + xValues(2)
InterPLinear = InterPLinear + xValues(3)
InterPLinear = InterPLinear + xValues(4)
InterPLinear = InterPLinear + xValues(5)
'I know that there will be only 5 values, will make it generic at a later stage.
End Function
In the spreadsheet I have created a short table with 5 rows and 1 column with values 1 to 5. In another cell I have entered the following =interplinear(A1:A5) and I have tried the following as well {=interplinear(A1:A5)} Could you perhaps tell me how I can correctly access this function so that it does not return #value
I have attached the file if it is needed
View 4 Replies
View Related
Nov 7, 2008
I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.
I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.
View 9 Replies
View Related
May 11, 2009
I have pasted 5 fields with some random values that are in A1 to E1. E is the column I am trying to populate to fill in the missing data for C.
I have written a function that will interpolate between 2 deltas using the dates. Assuming that June 09 will always have a corresponding delta (e.g. here 33) how can i populate the INTERP column using my YINT function. I can do this manually 1 at a time, but the already-filled cells in the Delta column will not always be the same and manually would take ages....
View 9 Replies
View Related
Aug 22, 2008
I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...
View 6 Replies
View Related
Oct 25, 2007
Actually, I've created the user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output.
Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
Worksheets("ITC").Select
'Make table into a range for VLookUp
Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
'If the given value does not match a table value exactly
On Error Resume Next.......................
View 4 Replies
View Related
Jan 14, 2010
I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies
View Related
Feb 27, 2009
I'm trying to find a way to enter the value of a cell from another worksheet into my currently active cell. The (obviously incorrect) code I'm working on looks something like this:
View 2 Replies
View Related
May 31, 2007
I would like to obtain the name of a workbook after activating it and then paste it into an input box.
How could I do this?
View 9 Replies
View Related
May 25, 2006
I am currently running XC running races and am wanting to know how i can have a quicker way of sorting out the best of sex, best of age group and best overall. The age groups are Under 18, under 30 and over 30. What can i do with v- lookup and other formulas to make it easier to record when they come in with out a long wait, i just want to be able to type in thier number and get all thier details so i can add the time straight in, then from there work out the winners instantly
View 2 Replies
View Related
Sep 4, 2006
Does anyone know the code for obtaining the filepath of the current workbook? Tried a search and can't find anything on this.
View 9 Replies
View Related
Nov 23, 2006
how to obtain what the formatting of a cell is, be it a date, number, custom, currency, etc? I want to acquire the format of the cell in vBA code, in order to display the value exactly as it was formatted in the cell when sending that value to a label on a UserForm or Field on a Microsoft Word form.
Example:
Cell A1 has the value 12/25/2006 formatted with the Date format: "mmmm-dd-yyyy" appearing as December 25, 2006
Cell A2 has the value 9.247 formatted with the Number format: "#.##" appearing as 9.25 to show only two-decimals
Cell A3 has the value 899, formatted with the Custom format: "00000" appearing as 00899 to represent a five-digit US zip code
Cell A4 has the value 1234.56 formatted with the Currency format: I think -"$#,##0.00_);($#,##0.00_)" appearing as $1,234.56
For rownumber = 1 To 4
Entry = Cells(rownumber,1).Value 'gets whatever in cell
Formatting = Cells(rownumber,1).Format 'makes no sense in vBa.
msgbox Entry & " formatted as " & Formatting
Next
View 8 Replies
View Related
Jan 25, 2013
How can I obtain the array values from a function?
For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
Redim datosColumnaIncio(Len(strSplit) - 1)
For iTemp = 1 To Len(strSplit)
datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1)
Next
End Function
So I want to know if its correct like that:
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
[Code] .....
So when I call the function:
VB : MsgBox splitText("F4")
It throws an error message :s what is wrong or how can I get the array values of a function ???
View 2 Replies
View Related
May 1, 2014
Excelforum.jpg
What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)
Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")
View 14 Replies
View Related
Jan 29, 2014
At any rate, for a statistics experiment, I need a way to obtain precisely 6 random, numeric values in a cell, separated by commas, with no duplicates.
For example, if cell A1 contains the following: 7, 12, 14, 7, 14, 3, then I need to remove the duplicate(s) -- in this case, the extra 7 and the extra 14. After the two duplicates are removed, I then would have to replace with two more values (non-duplicates, of course).
Here are the restrictions, in addition to having no duplicate values appear:
* The first 5 values need to be randomly drawn from values between 1 and 75, inclusive.
* The 6th value needs to be randomly drawn from values between 1 and 15 (inclusive).
* It would be nice - but not necessary - to have the first 5 values sorted in ascending order. For example: 7, 12, 14, 29, 70, 3.
View 7 Replies
View Related
Nov 21, 2007
I've assigned a single macro to numerous Form Checkboxes (not ActiveX). I'd like the macro to be able to identify the Checkbox calling it, so that if for example, if it were fired by clicking "Check Box 41", the macro should know that.
Is this possible, or must I use an ActiveX control?
View 9 Replies
View Related
Jan 6, 2009
I have been using the Formula 1 (see below) to obtain subtotals on a report, which works fine. Cell A4 has 1st July 2008 and cell A5 has 1st Aug 2008 entered as dates.
I wanted to confine the formula to a single row by using the MONTH() function using formula 2. I have spent a considerable amount of time trying to get this working with no success.
Is someone able to have a quick look at this and tell me what I am doing wrong.
Formula 1
=SUMPRODUCT((INDEX(Consolidated,0,16)="SI")*(INDEX(Consolidated,0,14)>=$A4)*(INDEX(Consolidated,0,14)
View 9 Replies
View Related
Jan 25, 2009
how it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,
View 9 Replies
View Related
Mar 4, 2010
I've been trying to get this to function correctly. The following code does pull out a list of all filepaths, but what I want to to is access the properties of the file.
Basically I select a customer from a combo box, and from this the code does a vlookup to determine what folder the reports are stored in. The list is then entered onto the worksheet 'FilesInReportFolder'. There is some commented out code as I've ben trying to come up with a simple way to remove the 'xls' from the end of the string, but din't worry about that.
I've found a website that suggests adding .name to the lineto get the name instead of the path, but what I really want is be able to pull out other details about the file.
Here's my
Private Sub ComboBox1_Change()
Combobox1value = ComboBox1.Value
Dim rng As Range
Dim RowNo As Integer
Dim NoOfFiles As Integer
Dim foundfilepath As Integer
Dim FilePath As String
View 9 Replies
View Related
Sep 7, 2006
Let's say Cell is a cell.
How do we select this cell's column? I tried :
Cell.Column
but it doesn't work. I have an error message.
View 7 Replies
View Related
May 29, 2009
i've looked on the threads and there are some exaples of this but not specfic enough.
i need code that allows me to browse my PC for Excel Files then select one file and put it's name into the activecell?
View 5 Replies
View Related
Mar 17, 2013
The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.
View 3 Replies
View Related
Apr 18, 2007
Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.
I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.
View 6 Replies
View Related
