Using intermediate results in Worksheet functions

[clare.moe]

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.

 

[Don Guillett]

=(O74-2080)+VLOOKUP((O74-2080),myTable,3,TRUE)
where c1 has your =o74-2080
=IF(LEN(TRIM(o74))<1,"",VLOOKUP(C1,mytable,3,0))

 

[MartinW]

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

 

[clare.moe]

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.

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.



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.

 

[MartinW]

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

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.

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.



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.

 

[clare.moe]

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.

I am concerned with a calibration that doesn't have
a linear correlation.

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

 

[MartinW]

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

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.

I am concerned with a calibration that doesn't have
a linear correlation.

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