Tricky data search within array

[Andy Sandford]

Hi All

I have a formula that converts the diameter of a circular duct into it's
equivalent rectangular counterpart. This formula is used to populate a
table, a section of which is detailed below:
Width
100 125 150 175 200 225 250 275 300 325
100 110 123 134 145 154 163 171 178 185 217
H 125 138 151 162 173 183 192 201 209 240
e 150 165 178 190 201 212 222 231 260
i 175 193 206 218 230 240 251 279
g 200 220 234 246 258 269 297
h 225 248 261 274 286 313
t 250 275 289 301 329
275 303 316 344
300 330 358

The top row of the table is the width of the rectangular duct, the left hand
column is the height.
The cells within the body of the table are the circular duct equivalents.

e.g. The rectangular equivalent to a circular duct of diameter 201mm is
therefore either 275mm x 125mm OR 225mm x 150mm

I need to search the body of the table for the closest match to a given
diameter (n1) and return the numbers in the corresponding top row and
left-hand column (width and height).

I need to return a single result based on the following constraints:

1) The initial circular match in the table needs to be equal to OR higher
than n1.
2) The height of the rectangular element needs to be less than or equal to a
specified value (h).
3) The ratio of width to height needs to be as close to 1:1 as possible.

e.g. Required circular duct diameter (n1) = 233mm, Maximum height of
equivalent rectangular duct (h) <= 175mm

Closest selection in the body of the table is 234mm, but this does not meet
point (2) maximum height (h)

The next nearest selection is 240mm, but this occurs twice.

Based on aspect ratio, point (3), the best solution is therefore 275mm wide
by 175mm high (1:1.57), rather than 325mm x 125mm (1:2.60).

I hope I've given you guys enough info.

Thanks to you all in advance!

Andy

 

[HS Hartkamp]

Andy,

This seems very apt to do by brain, not by computer. Human brains are much
better than computers at multi-constraint complex lookups.
You said you have a formula, and the table is based on that formula, so I
have the gut feeling you should work from the formula and not from the
table.

I asssume this formula is based on geometry of the cross section, so you
should be able to -very easily- convert from rectangular to circular. (do
this first to test your formulas).

To work the other way around, the problem then becomes one of breaking a
number into its possible factors. By building a table of factors and testing
for integer numbers (=valid solutions) you should be able to do this. First
column = known widthts, second column is calculated heigths. Combine
MIN(ratio) and index and match to pick the correct solution.
Alternatively, perhaps using "goal seeking" might work, but it's rather
tricky to make sure you get proper solutions in fixed-interval integers
(possible duct widths and lengths)

If this does not seem possible, what does the formula look like ?


The less scientific, more practical approach is to do it semi-automatic.
Put conditional formatting on the table cells. Type the circular diameter
somewhere, and have the formatting grey-out all table cells that are clearly
not matching. (Conditional formatting: FORMULA = if((<cell reference to
diameter>-<table cell>) < X ; True ; Untrue) where x is some maximum allowed
margin. Condition if True would be light grey text color.). Other
constraints can be added.
If you type a new diameter, the cells change color, and some remain black.
The blacks are possible options. Pick the one closest to the diagonal of the
table because that has the lowest height-to-width ratio.


Bas Hartkamp.

 

[Andy Sandford]

Hi Bas, thanks for your reply.

The formula I'm using to convert circular to rectangular (for equivalent
flow rate) is:

d=1.265*((a*b)^3/(a+b))^0.2

Where (d) is the diameter of the circular duct and (a) and (b) are the width
and height of the equivalent rectangular duct respectively.

(d) is found by solving the following equation:

Q=-4*(0.257*p1*d^5)^0.5*LOG(0.000015/(3.7*d)+(0.00001487*d)/(0.257*p1*d^5)^0.5)

Where (Q) is the desired volume flow rate in m3/s and (p1) is the desired
pressure drop for that flow rate per linear metre of duct in Pascals.

Rather than solve this equation for (d), I've cheated by creating a table
with a (very long!) column of every conceivable diameter (d) from 1mm to
2500mm in 1mm increments and calculate (Q) for each.

I then use VLOOKUP with my desired value of (Q) to find the relevant value
for (d).

This gives me reference circular duct diameter from which I can then
calculate the equivalent rectangular duct using the table I've created using
d=1.265*((a*b)^3/(a+b))^0.2 (another cheat!).

I'm sure there must be an easier way, but It's been a long time since I did
maths at college!

If I could solve the equation
Q=-4*(0.257*p1*d^5)^0.5*LOG(0.000015/(3.7*d)+(0.00001487*d)/(0.257*p1*d^5)^0.5)
for (d) I'd be VERY happy!

If I could solve d=1.265*((a*b)^3/(a+b))^0.2 for (a) I'd be happier still!

Endless searches on Google have yielded nothing as yet...

Thanks again for your help

Andy

 

[kcc]

This is a bit messy, but it should work. It would require that
the lower left triangle that is now blank be filled with zeros.
I am also making what is potentially a big assumption, that the
width index and height index are the same so that I can
assume that if there are multiple match, the one in the highest
row number is closest to the desired 1:1 ratio.
I've put the width index in G1010, height in F11:F20 so that
the first table entry of 110 is in G11.
C1=The desired circular size (Input)
C2=Maximum Height (Input)

A11=IF(HLOOKUP($C$1,G1111,1)=$C$1,$C$1,INDEX(G1111,1,MATCH($C$1,G1111)+1))
and copy down.
A11 is the best match for the given row.
B11=IF(ISERR(A11),10^10,IF(F11>$C$2,10^10,A11-$C$1)) and copy down.
B11 is difference between the desired n1 and the closest value, with 10^10
if > max height or no match.
C11=MATCH(A11,G1111) and copy down.
C11 tells which column matches the found value

C3=MIN(B11:B19) is the error in the closest match
C4={MAX(IF(B11:B19=C3,ROW(B11:B19)-ROW(B11)+1,0))} largest row matching C3
(array formula)
C5=INDEX(C11:C19,C4) column of match
C6=INDEX(F11:F19,C4) Height of answer
C7=INDEX(G1010,C5) Width of answer

If the assumption is wrong you would have to add a column of
ratios for the closest match and find the min. Some additional
error checking should be added. This returns errors or the
biggest height if the is no match.

Not really understanding your work, I may be way off base, but
I do wonder if the closest match to n1 is really the best first criteria.
For instance if n1 is 185, the answer is 100 x 300.
However, 175 x 175 allows more flow at 193 and requires less
material with a perimeter of 700 vs 800.

kcc

 

[Andy Sandford]

Hi kcc

Thanks for your help

It took me a while to get my head around it, but I think I can see what
you're doing now after applying it to the whole table (100 x 100 entries).

You're right about my logic! - I need to alter it to return the closest
match with the lowest aspect ratio that fits within the height constraint.

Basically, this means isolating all of the rows that fall within the max
height constraint, then finding the leftmost value within those rows that is
greater than the required diameter. The corresponding row and column are the
answers... I think!

I'll give it a go!

Thanks again

Andy