need help for 3 value table 20$ paypal gift card

[turkyildiz]

Hi guys here is the problem below is the 3 value chart for Boeing
747-400 side door, As you may see
Height, Length and Width are the factors determining factors. All I
need is by entering L W H (i.e 420Lx 86W x 96H) figures on the side
then excel to decide if this piece would fit through the 747 Side door.
for a better looking file please check out
www.nzcargoops.com/sidedoorchart.xls
As I promised whomever gives me correct formula first i will send a
20$ paypal e-mail.

Regards/turkyildiz

Height width 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 134
Length
118 438 412 388 367 347 331 315 302 290 280 269 259 249 239 228 219 208 198 188 179 169 161 152 144 137
116 465 434 408 363 386 346 329 314 300 299 278 368 257 248 237 228 218 208 197 188 178 169 159 151 145
114 492 458 429 381 404 361 342 326 311 299 287 277 266 256 246 237 227 217 206 196 186 177 165 158 153
112 512 47 444 392 417 370 350 334 318 306 293 282 271 261 251 242 232 222 211 202 192 182 171 163 157
110 531 492 459 402 429 380 358 342 325 312 299 287 276 266 255 246 237 226 216 207 197 187 177 168 161
108 550 509 441 414 441 391 369 350 332 318 304 293 281 271 260 251 241 231 221 212 202 192 182 173 165
106 561 525 486 424 455 399 375 356 339 326 310 297 286 275 264 254 244 234 224 215 205 196 185 176 170
104 572 543 500 435 468 409 384 363 346 333 316 303 291 279 268 258 248 239 228 219 209 199 189 181 175
102 582 561 533 445 505 418 393 373 354 338 322 309 296 284 273 263 252 243 233 224 214 204 192 185 180
100 588 574 528 457 492 428 400 378 360 346 328 313 300 288 277 267 256 246 235 227 216 207 197 188 183
98 594 586 542 468 505 438 408 386 367 353 334 318 304 292 280 270 260 250 238 230 219 210 202 192 186
0-96 600 600 556 480 517 447 418 395 373 355 338 323 309 297 285 275 264 254 242 232 222 213 204 196 192

 

[Nick Hodge]

turkyildiz

I had to re-sort the data (After unmerging the data in columns A+B) and set
up a table for L, W, H in A2, B2, C2 on sheet2 and came up with this. If it
works, or/and I am first donate the $20 to charity

Formula in D2 of Sheet2

=IF(OR(A2>MAX(Sheet1!$C$3:$AA$61),C2>MAX(Sheet1!$A$3:$B$61),B2>MAX(Sheet1!$C$1:$AA$1)),"No",IF(VLOOKUP($C$2,Sheet1!$A$3:$AA$61,MATCH($B$2,Sheet1!$C$1:$AA$1,1)+2)<A2,"No","Yes"))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[turkyildiz]

would you please e-mail me ( (e-mail address removed)) the sheet you made
along with you e-mail address for paypal. if works i will send you 20$
to address provided.

regards/
turkyildiz

 

[turkyildiz]

Happy Holidays,

Value on cell N4 is correct but i am not sure what do you mean by
problem with integrity, as per answer by Nick Hodge 600 x 20 x 96
should display yes but it displays no. If needed actual dimensions of
747 side door is 134.2 inches wide and 119 inches high.

 

[Nick Hodge]

I'm working on a better solution, but I am having to swing the data around
as I want to make sure it is always taking the size larger, currently it is
taking the size smaller which is no good

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Nick Hodge]

turkyildiz

I ended up reordering the Width and kept the height in descending order.
(That effectively puts both width and height in descending order). The
formula I now use is

=IF(OR(A2>MAX(Data!$C$3:$AA$61),C2>MAX(Data!$A$3:$B$61),B2>MAX(Data!$C$1:$AA$1)),"No",IF(OFFSET(Data!$C$3:$AA$61,MATCH($C$2,Data!$A$3:$A$61,-1)-1,MATCH(B2,Data!$C$1:$AA$1,-1)-1,1,1)<A2,"No","Yes"))

Testing seems to work. Let me know

Workbook mailed

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk