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 |

1/12/2004 7:19:37 PM

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...

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...

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,...

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 (...

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...

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...

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...

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...

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, "", ".") str = Replace(str, "", ".") 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...

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...

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...

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 ...

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...

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 ...

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@...

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 >...

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 ...

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

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...

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. -...

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 ...

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 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...

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...

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...