Compare & Interpolate Values

Table with two dependent columns (example.  Height1 vs. Capacity1). 
Now I want to create a new, two column table with a given Capacity,
lets call it Capacity2).

So these are my givens:
 
Height 1    	Capacity 1
10	        500
20	        600
30	        900

Capacity 2	Height 2   
550	
610	
850	
 
Now, I want to create a function with will take my Capacity 2 value,
compare it with Capacity 1 and interpolate the appropriate Height 2
value (from Height 1).
0
eszczepa (1)
1/12/2004 5:30:50 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
270 Views

Similar Articles

[PageSpeed] 15

Milia,

Set up your table so that it is the reverse of what you have:

Capacity 1  Height 1
  500            10
  600            20
  900            30

Then put the volumes into cells A7:A9, (the 550).  Then use this
formula in B7 (remove any spurious line-breaks thrown in by our mail
readers)

=INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),2)+(INDEX($A$2:$B$4,MATCH(A7,$A$2
:$A$4)+1,2)-INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),2))*((A7-INDEX($A$2:$B
$4,MATCH(A7,$A$2:$A$4),1))/(INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4)+1,1)-I
NDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),1)))

and copy to B8 and B9.

HTH,
Bernie
MS Excel MVP

"Milia" <eszczepa@hotmail.com> wrote in message
news:4cda7a8a.0401120930.515f0c87@posting.google.com...
> Table with two dependent columns (example.  Height1 vs. Capacity1).
> Now I want to create a new, two column table with a given Capacity,
> lets call it Capacity2).
>
> So these are my givens:
>
> Height 1    Capacity 1
> 10         500
> 20         600
> 30         900
>
> Capacity 2 Height 2
> 550
> 610
> 850
>
> Now, I want to create a function with will take my Capacity 2 value,
> compare it with Capacity 1 and interpolate the appropriate Height 2
> value (from Height 1).


0
Bernie
1/12/2004 7:19:37 PM
Reply:

Similar Artilces:

Negative value in Excel
I would like value in a certain rage of cells to always be negativ without adding minus sign when enter the value. Does anyone know how t set this??? Many thanks -- Message posted from http://www.ExcelForum.com Adhanya, One solution is to put the values in straightaway (as positive), and simply make them look as though they're negative. Select the cells, Format, Cells, Number, Custom, and use something like: -General -0.00 Then in any formulas that refer to them, negate them so they will be treated as negative. Or an event macro can negate them as they're entered. -- Earl Kios...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

copy paste value
I have a colum of vlookup numeric data and want to add it up, but each cell may not return a value depending on how many items are needed. I belive I will need to add a colum in and grab the numeric data that is returned and then use a formula to do a copy paste value and if no value equal zero. I am not sure how to do this thoe. here is the vlookup formula i am using. Can you please help =IF(ISNA(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,FALSE)),"",(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,...

Value of x axis
I want to create a chart where the X axis displays a letter instead of a number. The repesentation will be constant (e.g. A=5, B=4, C=3). Is there anyway to do this? Thank you, Kate - I answered the same question just this past week: http://www.google.com/groups?as_umsgid=%23jDCZiMsEHA.832@TK2MSFTNGP10.phx.gbl - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Kate wrote: > I want to create a chart where the X axis displays a letter instead of > a number. The repesentation will be constant (...

Auto Label Y Axis Values
I am producing a dynamic chart and would like to be able to specify the labels for the y axis of my chart automatically (based upon a formula), rather than letting Excel set the scale. I believe that to do this I need a macro - but I am new to using these and dont really know where to start. Basically the chart is on a sheet called Graph and the data that I want to use for the minimum and maximums are in cells T4 and T5 on a sheet called tables (although I have also named the cells Min and Max too). Any help would be greatly appreciated. Thanks. Hi, You need to use code. http://pelt...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

Pivot Chart
Hello, I am trying to create a stacked area Pivot Chart from a Pivot Table. I have negative values but am having trouble getting them to be displayed properly in the pivot chart. Currently, the negative values are displayed at the top and are "eating into" the positive series. I need the negative values to be displayed below the x-axis. Any help would be greatly appreciated. Aleksandr, This may be silly to ask, but... Did you try and adjust the y-axis scale from 0 to some number to a negative value (greater than the lowest negative value) to some positive value. Ex...

How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "&#x19;", ".") str = Replace(str, "&#x18;", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Select case syntax for continuous range values?
I know you can use Select case statements to cover a range of integer values, such as: Case 100 To 500 Case 501 to 650 etc However, I'm not clear on how to work with non-integer values to ensure that every possible value is covered, while ensuring no overlap in categories. With an IF statement I might use: If x >100 and x <=500 elseif x >500 and x <=650 which would properly assign a value of 500.4395 to the second condition Is there a way to do this with select case? Or is my best option to try to use more decimals than the data might need, e.g. C...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Highlight field when value is changed
I have multiple rows of data, each one starting with a number. If the number in the front of the row changes, I need that whole row to be highlighted. Then I need a check box on the other side of the row so that I can turn off (reset) the highlighting. 1 2 3 4 a # Name S Reset b 21 Jane Hanson F c 22 Mark Johnson M d 23 Fred Lee M I know I can do this with track changes, but we can't use that because of other issues. Can anyone tell me where I can get info on trying to make something like this? I t...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

How to convert to absolute value?
Hello! How do I convert numbers to absolute value? Thanks Edit - Copy. Edit - PasteSpecial - Values - OK. Or do you want a code solution ? HTH. Best wishes Harald "Betsy Marlow" <cmarlow22@bellsouth.net> skrev i melding news:5WmHg.12873$j8.11911@bignews7.bellsouth.net... > Hello! > > How do I convert numbers to absolute value? > > Thanks > > Print abs(-12) 12 On Thu, 24 Aug 2006 15:16:31 -0400, "Betsy Marlow" <cmarlow22@bellsouth.net> wrote: >Hello! > >How do I convert numbers to absolute value? > >Thanks >...

Select null values in Crystal Report
I want to select those records in a Crystal Report where the country is not filled. I can select those where it is filled, however I get zero record back when I want to select where it is not filled. I use the following selection formula in the record selection part: Length ({account.address1_country}) < 1 Any idea is appreciated. Thanks, Miklos ...

How to Assign Values from Access field to a MS Word Field?
Hi I need to know how to assign values from Access field to a MS Word field? Thanks ...

Giving Null a value in a VBA stamtement
Hi all, I am writing some VBA code in Access03 in a fields after update event. The field types are decimal (18,1). I am trying to write a SQL statement which looks like this: Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg = ([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]! [SizeUGAliquotSize1])+([tblStorageShip]! [NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]); And if one of the fields is a NULL i get a NULL return for that record. Can I somehow set NULL to euqal 0 for this set of code instruction so anytime it wencounters a null while exec...

Inserting cell value into text....
I am trying to create text in excel where at certain points a cell value needs to be inserted and change as the original cell value changes. How can I do this and maintain the integrity of the text formatting? Thank you so much! Try something like - ="Answer as shown in cell A10 ; "&A10&", or thereabouts." HTH, Andy Support Boris - visit http://www.ncadc.org.uk/letters/newszine36/boris.html ="abcdef and the value is " & TEXT(A1,"$#,##0.00") will allow you to specify the format of the number that is shown in the resulting string. -...

Chart does NOT show data entered for the values represented
I continue to find a problem in excel 2007 that was not present in 2003. I will input 2or more columns and rows of data to create a chart, then create the chart; but IF MORE THAN ONE data path is selected to be charted, the other data paths are charted incorrectly. They do not reflect the values in the cells! Obviously then, my charts are wrong and I can't rely on them for analysis. Please respond if this has happened to you and you know how to correct for this. I am very frustrated. -- Judi Hi Judi, Data Path? what is this, are your charts refering to other workbooks? Why ...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

I want to ignore invalid values in scatter chart
I have an x-y scatter chart which uses values calculated from other sheets in the workbook. Sometimes these values are invalid, and therefore I use "IF" to render the appropriate cell blank. The entire series then disappears from the chart. I just want that point to bu uncharted. Any ideas? Hi, I can't reproduce you problem. Depending on the formula used my data points either do not show, as intended, or have the value zero. Are you using a formula like this? =IF( test , value , NA() ) Cheers Andy Phil Rowe wrote: > I have an x-y scatter chart which uses values ca...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

Future Value function with differenct cash flows
Excel does not seem to have a FV function or variant that allows you to compute the FV of different cash flows at regular periodic intervals given a constant rate. Is there such a function? I can think of several applications where the FV of CF's are needed wherein CF's are at both even and uneven intervals or said another way, at set periods versus random periods and at different CF amounts. There are several work around solutions I can think of, but they tend to be cumbersome. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggest...