Working with a large excel data table

Hi everybody,

I was given a large excel file with 1,000 rows and 7 columns that are arranged in a way to show the total volume of a cylinder at .01' increments ranging from 1' to 10'. The cylinder's floor is sloped, so there was some math done in excel to get the data points. We can assume they are correct.

My question - Is there a way to create a lookup table in Studio5000 (using L8x cpu) that will compare the transmitter's level against a known data point? Problem is, each .01' increment is given a volume, so I would have to add up all the data points before it to get the "total volume". I'm just asking for advice on how to approach this before I waste a bunch of time.


Thanks

Wait for Peter , he is the one.

The FAL instruction would work (use the level transmitter as the end point of the FAL instruction and the equation should sum the values into a new tag), but I just have to ask, why?

Why not just come up with an equation, geometrically? You could use the tank dimensions or even the data and work backwards to come up with the equation. Heck, you could even do regression in Excel to come up with the equation if you don't know the diameter of the tank and don't want to come up with equations from data (sloped floor section volume is probably a function of level squared, main body is a function of level). This shouldn't be particularly difficult for the Excel-savvy or someone who is reasonably good at Google-fu.

A lookup table is nice, but not maintainable and takes a lot of memory and compute time (though, with an L8 compute time, and likely memory, isn't probably going to be an issue). It is a bodge-y solution IMO, unless there is a good reason an equation can't be used.

Assuming you have eXcel (or can upload the workbook to Google Sheets), an eighth column can do the accumulation. e.g. if column G is the delta volume between levels, and row 2 is the first row, then put the formulae

• =G2 in cell H2;
• =G3+H2 in cell H3;

and copy the formula in H3 to H4:H902 (or whatever the last row is).


Then do a fit, or an equation as suggested by JLand, to get one or more formulas. What level of accuracy is required? Does the cylinder have flat, dished, or ellipsoidal ends? Is the central axis of the cylinder near vertical or near horizontal?

Just tinkering with this website and Excel for a few minutes:

https://arachnoid.com/tank_slope_bottom/index.html

showed me that a sloped bottom is a third order polynomial.

What I recommend doing is to run the summation that Drbit recommended in Excel and running two regressions on it. The first is a 3rd order polynomial that starts at tank empty and ends at the top of the sloped section. You should get a really high R2 value, at least 0.98 if your data is good. The second regression starts at the end of the sloped section and ends when the tank is full. This should be a linear equation.

Then, in the PLC, run the first (3rd order) polynomial when your level is in the sloped section, and run the second (linear) equation when your level is in the non-sloped section, adding to it the volume of the sloped section. The value of the volume at the transition should be equal across the two equations, so the end result shouldn't have any sharp changes in volume when you transition from sloped to non-sloped.

If you are unsure where the sloped section ends, plotting the change in volume as a function of percent in Excel should show it quite clearly.

Ah. By sloped bottom I thought OP meant it is a cylindrical prism (ends of tank perpendicular to axis), but that it was mounted without that axis perfectly vertical or horizontal.

But either way, JLand is right, if the axis is near vertical, the calc is linear with level between the point where the bottom is filled and the top starts to be filled.

Doesn't Logix have a table lookup/interpolation instruction of some sort? I can't seem to find it; maybe use an axis camming thing? Only the sloped section needs to be modeled with many data points (assuming the axis is not near horizontal).
_

I suppose neither of us know exactly what the tank looks like, but either way I think both our methods will work most any cases.

JLand said:

but I just have to ask, why?

Click to expand...

I was just trying to reduce the amount of time spent trying to put this together. I thought that by already having a compiled data set, I could just point to a number. It seems it's far more complicated than it's worth, so now I am looking to just compute it in the PLC.

Just to clarify, the cylinder is roughly 10,000' long and has a gradual, linear 0.001 slope from front to back with a 25' diameter. The end points are both vertical so that makes it much easier to calculate.

So there will be two equations - one with the sloped section, and one above the sloped section.

Thank you all for the help

#FFFFFF said:

Just to clarify, the cylinder is roughly 10,000' long and has a gradual, linear 0.001 slope from front to back with a 25' diameter. The end points are both vertical so that makes it much easier to calculate.

Click to expand...

Ten-thousand feet long with a slope of 0.001, twenty-five feet Ø, end cap planes are perpendicular to a horizontal plane.

So

• one end is ten feet higher than the other,
• and we are ignoring the curvature of the (gravitational field around the) earth?

drbitboy said:

Ten-thousand feet long with a slope of 0.001, twenty-five feet Ø, end cap planes are perpendicular to a horizontal plane.

So

• one end is ten feet higher than the other,
• and we are ignoring the curvature of the (gravitational field around the) earth?

Click to expand...

haha I'm not too sure about that last point .. this is a combined sewer overflow tunnel that runs hundreds of feet underground through a city.

well, if you can paste the accumulated volume column ("H") values into a 1000-element array, it is a linear lookup from level to volume. e.g.

Not an AB guy, but:

If the excel table is a calculation, surely you can see it (or reverse engineer it) and type it into one/several compute blocks. Worst case you could probably do the math in Structured Text. Gotta be faster to type in math equations than 1000 rows of numbers, less memory, and less error prone.


If the excel table is based on measured data of a weird shape from adding .1L to the tank at a time, or something like that, lookup table might be your only option. It'd still be worth checking how one would calculate it. Geometry can be weird, but usually still solvable

Not sure I understand the question, but can you make an array[0..1000] which is 0 to 10' increments 0.01', and the lookup result of 5.55' is array[555]?

If the Excel data is sorted ( as in arranged in a ascending or descending pattern ) then yes. A rudimentary look up table could be established. Given the large data it would have to transverse it would most definitely have to be written in ST. I'd recommend using a binary search style code. I've written something similar for the l8x I have sitting in front of me.