Excel VLOOKUP using Do-More

[Dean]

Where I work we manufacture Purified Water. We adhere to USP PW limits for quality. One parameter is conductivity. The limit is temperature dependent. In other words, the conductivity limit changes according to water temperature, as defined by the attached image USPLimit. For instance if the water temperature is greater than or equal to 15C, and less than 20C, the conductivity limit is 1.0 micro-Siemens/cm. if the temperature is greater than or equal to 20C and less than 25C the limit is 1.1 micro-Siemens/cm, and so on. I use Excel to analyze batches of data, and look at each data pair of temperature, and conductivity, to see if the pair meets the established limit. The easiest way to do this in Excel is to use the VLOOKUP function. Most of you probably know how it works already, but if not, basically you compare a value to column 1, (Temp), and return the value in column 2 (Cond) that shares the row of column 1 that matches your value. You can then insert your VLOOKUP function into other formulas to do whatever you want. So anyway, I wanted to try to recreate a VLOOKUP in a Do-More, so I did. See attached VLOOKUP.zip. I used indirect addressing thinking I might be able to simplify pointing to the columns, but couldn't really figure that out. Didn't really need the extra step, but I left it that way. I'm sure that there is a better way to do this. Anyway, if anybody has the time, I would appreciate any feedback.

[BobO]

Doesn't answer the VLOOKUP question, but this seems like a great application for the LERP instruction.

[Controls Guy]

You can now do math in indices in MATH boxes, so:

1) R[t/5 + table_base] or create a table dedicated to the values to eliminate the base address, or
2) R[t] (+ base address, if used), and just fill in values for each degree, even if the values for consecutive temps are sometimes the same, or
3) COUNTIFLE(t) finds the row index, so long as the values are in order, so R[COUNTIFLE(t)] gives the limit, or
4) if you're allowed to interpolate, derive the equation between temp and allowed conductivity.

[Controls Guy]

Looks like the first four are linear and the last one is an exception, so just do linear and add the extra 0.1 based on an IF().

If you don't want to do that, use

C_max = 6.58 x 10^-6 t⁴ - 3.28 x 10^-4 t³ + 5.73 x 10^-3 t² - 2.08 x 10^-2 t + 0.797

[franji1]

LERP is the continuous version of Excel's quantized VLOOKUP.

If you want less accuracy, we could implement VLOOKUP  .

If you need help mapping your VLOOKUP table to a LERP table, we can definitely discuss it here.  For example, should the MIDPOINT of the VLOOKUP INPUT RANGE match map to the OUTPUT of the VLOOKUP, or should the START, or ?

[Dean]

I will look at the LERP instruction, but in this case, interpolation is not allowed. For example the limit @ 20, 21,22,23,and 24 Celsius is 1.1. Once the temp goes to 25, then you get 1.3. The rules are strict. If your temp is 24.99999.... the limit is 1.1, but it's also 1.1 at 20.00000001. This is new a few years ago, there was a lot of confusion surrounding conductivity testing of PW generation systems, different methods, different types of sensors, some analyzers/meters used a temperature compensated algorithm, some didn't, some companies used grab samples, some used inline sensors, and nobody was really sure if an auditor was going to accept your measurement method, and pass/fail test or not. The USP did a huge study and created a standard that was consistent, easy to understand, and implement. That's probably more than anybody cares to know about conductivity of Purified Water. Lord knows I've spent wayy too much time thinking about it.

[franji1]

For example the limit @ 20, 21,22,23,and 24 Celsius is 1.1. Once the temp goes to 25, then you get 1.3. The rules are strict. If your temp is 24.99999.... the limit is 1.1, but it's also 1.1 at 20.00000001.

Understood.  The table is small enough that you can do it in straight ladder logic (vs. doing it "generically" with a table with variable length, that contained variable limits and variable outputs)

[Controls Guy]

Don't know LERP, so for all I know that's the better method, but I can't speak to that, so I'll just tell you what I'd do.

You can use the properties of integer math to get the steps.

If your temperature is in V100 and your Cmax values are in R1000-R1004, then

Code: [Select]

Cmax = R[(V100 / 5) + 999]
OR just calculate it

Code: [Select]

MATH R1000 (V100 / 5) * 0.1 + 0.7 + IF(V100 = 25, 0.1, 0)
Not sure what happens if you start out in integer and then the expression goes real, so you might have to do V100/5 as a separate step and store the integer result, then do the other steps in a second box, but I don't think so.  The quantization from the integer math gets you your steps for free, and will return the same value over the range, as you've described.  You'll also may need to treat the edge conditions where temp is <5 or >25.  So annoying that they have the little hockey stick at T=25 or it would be even cleaner.

If your temp is a real, you'll need to make it an integer first to use this property of integer math.

[BobO]

Don't know LERP, so for all I know that's the better method...

Multi-point linear interpolation X to Y translation. Primarily intended for approximating non-linear functions.

[Dean]

Thanks Controls Guy,I'll study your suggestion.