 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Using intermediate results in Worksheet functions
Apologies if the answer to this question is in some 'obvious' place -- I've not found it if it is! Is there a provision (other than using some arbitrary cell swomewhere) for creating (one or more) intermediate results to be used in worksheet function evaluation? (I'm using Excell 2003 SP3) An example formula would be: =(O74-2080)+VLOOKUP((O74-2080),myTable,3,TRUE) What I'd like to be able to do is only enter (O74+2080) once (in the interest of simplifying possible future editing.) By way of explanation: -- 'myTable' is a named range containing an interpolation table consisting of the columns 'True Load', 'Dial Reading' and 'Difference' -- The application is generation of a worksheet showing the actual target dial value expected after adjusting for tare weight and a non- linear calibration value. =(O74-2080)+VLOOKUP((O74-2080),myTable,3,TRUE) where c1 has your =o74-2080 =IF(LEN(TRIM(o74))<1,"",VLOOKUP(C1,mytable,3,0))  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Apologies if the answer to this question is in some 'obvious' place -- > I've not found it if it is! [quoted text clipped - 20 lines] > target dial value expected after adjusting for tare weight and a non- > linear calibration value. Hi Clare, Sounds like you are talking about some sort of test with a proving ring. If that is the case there will be an equation that fits your calibration data, and you should be able to do away with the VLOOKUP. Please post more detail of what you are trying to achieve. HTH Martin > Apologies if the answer to this question is in some 'obvious' place -- > I've not found it if it is! [quoted text clipped - 20 lines] > target dial value expected after adjusting for tare weight and a non- > linear calibration value. > Hi Clare, > [quoted text clipped - 6 lines] > Please post more detail of what you are trying > to achieve. Thanks for the interest! I'll try to explain better. From Don's suggestion it looks like I didn't describe my question as well as I thought I did! Yes, I am load testing; *but* the calibration data is erratic. I've forgotten my math terminology, but I suspect that any equation that would properly graph the calibration data would be a complex multiple term function -- VLOOKUP is at least straight forward. The specification gives me a tolerance of 2%; at a quick glance the calibration data varies from 0.975% to 2.06% -- so the variance eats between half and all of my allowable tolerance. The function from my OP works; I was simply wondering if I could eliminate the repeated term without using a cell to hold said intermediate term. > > =(O74-2080)+VLOOKUP((O74-2080),myTable,3,TRUE) Column 'O' (I just happened to be in row 74) holds the specified test load that must be imposed on the piece under test. 2080 is a constant value -- the actual weight of that portion of the test apparatus that has to be removed during the calibration process; and column 3 of 'myTable' is the calibration correction for the 'nearest' calibration point. The formula is giving me the actual indicator reading needed to apply the specified test load. Sample from myTable: VLOOKUP table; range = True (ie, return largest match .LE. Lookup value) True Dial Load Reading Offset 0 0 0 8000 8086 86 9000 9098 98 10000 10110 110 11000 11150 150 Sample of test worksheet (using above formula): 3/4 Test Max Load Load Load 8146 11590 18262 10210 14430 22324 18558 25406 37920 24032 32580 48560 I understand how to use another cell to hold an intermediate value; in this case it would require the addition of another column to my worksheet, as the value in column 'O' is a calculated result which is needed in other reports based on this data. Thanks again for the interest. OK, I think I see what you mean and yes, the only way to do that is to store your intermediate value in a separate cell and then reference that cell in your formula. I can see why you use a VLOOKUP it does simplify things for others to follow. I am concerned with a calibration that doesn't have a linear correlation. If you are using a dual ring then you will have two lines, one for the primary ring and another for when the secondary ring kicks in, but both lines should have a linear correlation. After all that is what a proving ring does, it measures the strength of a constantly increasing load. I would be analysing your calibration data by plotting it on an XY scatter chart (probably - dial gauge readings against true load) If that plot doesn't form into straight lines, then you will need to have your calibration checked. HTH Martin On Feb 5, 2:06 am, "MartinW" <m...@hotmail.invalid> wrote: > Hi Clare, > [quoted text clipped - 6 lines] > Please post more detail of what you are trying > to achieve. Thanks for the interest! I'll try to explain better. From Don's suggestion it looks like I didn't describe my question as well as I thought I did! Yes, I am load testing; *but* the calibration data is erratic. I've forgotten my math terminology, but I suspect that any equation that would properly graph the calibration data would be a complex multiple term function -- VLOOKUP is at least straight forward. The specification gives me a tolerance of 2%; at a quick glance the calibration data varies from 0.975% to 2.06% -- so the variance eats between half and all of my allowable tolerance. The function from my OP works; I was simply wondering if I could eliminate the repeated term without using a cell to hold said intermediate term. > > =(O74-2080)+VLOOKUP((O74-2080),myTable,3,TRUE) Column 'O' (I just happened to be in row 74) holds the specified test load that must be imposed on the piece under test. 2080 is a constant value -- the actual weight of that portion of the test apparatus that has to be removed during the calibration process; and column 3 of 'myTable' is the calibration correction for the 'nearest' calibration point. The formula is giving me the actual indicator reading needed to apply the specified test load. Sample from myTable: VLOOKUP table; range = True (ie, return largest match .LE. Lookup value) True Dial Load Reading Offset 0 0 0 8000 8086 86 9000 9098 98 10000 10110 110 11000 11150 150 Sample of test worksheet (using above formula): 3/4 Test Max Load Load Load 8146 11590 18262 10210 14430 22324 18558 25406 37920 24032 32580 48560 I understand how to use another cell to hold an intermediate value; in this case it would require the addition of another column to my worksheet, as the value in column 'O' is a calculated result which is needed in other reports based on this data. Thanks again for the interest. > OK, I think I see what you mean and yes, > the only way to do that is to store your > intermediate value in a separate cell and > then reference that cell in your formula. I suspected as much; thank you for confirming! > I can see why you use a VLOOKUP it does > simplify things for others to follow. That does get to be an important factor soemtime, doesn't it? <grin> > I am concerned with a calibration that doesn't have > a linear correlation. <snip> > I would be > analysing your calibration data by plotting it on an > XY scatter chart (probably - dial gauge readings against true load) > If that plot doesn't form into straight lines, then you will > need to have your calibration checked. I'm not familiar with your discussion of proving ring (it does make sense, tho!) Actually, I'm fairly new to this strength testing business but apparently in the concrete pipe testing segment of practical testing science non-linearity is common enough that provision for "interpolation tables" is written into the governing ASTM standard. The company that calibrates our testers for us explained to me that over time coiled tube hydraulic pressure guages develop idiosyncrisies that are peculiar to the individual guage; apparently related to "typical" loading ranges and so forth. So: I understand that I will be living with tabular calibration data forever! Thanks much for your help, I appreciate it! CM OK glad it is all OK. Sorry if I sounded a bit alarmist, it's just that in my industry, little idiosyncrasies in testing gear would cause major idiosyncrasies in our heart rates <g> We replace equipment at the very first hint of fatigue. Happy Testing Martin On Feb 5, 3:25 pm, "MartinW" <m...@hotmail.invalid> wrote: > OK, I think I see what you mean and yes, > the only way to do that is to store your > intermediate value in a separate cell and > then reference that cell in your formula. I suspected as much; thank you for confirming! > I can see why you use a VLOOKUP it does > simplify things for others to follow. That does get to be an important factor soemtime, doesn't it? <grin> > I am concerned with a calibration that doesn't have > a linear correlation. <snip> > I would be > analysing your calibration data by plotting it on an > XY scatter chart (probably - dial gauge readings against true load) > If that plot doesn't form into straight lines, then you will > need to have your calibration checked. I'm not familiar with your discussion of proving ring (it does make sense, tho!) Actually, I'm fairly new to this strength testing business but apparently in the concrete pipe testing segment of practical testing science non-linearity is common enough that provision for "interpolation tables" is written into the governing ASTM standard. The company that calibrates our testers for us explained to me that over time coiled tube hydraulic pressure guages develop idiosyncrisies that are peculiar to the individual guage; apparently related to "typical" loading ranges and so forth. So: I understand that I will be living with tabular calibration data forever! Thanks much for your help, I appreciate it! CM |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.