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

wrote in message
...
On Feb 5, 2:06 am, "MartinW" wrote:
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.




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