Need to pick numbers in a range of numbers

[Wing-it]

hello folks, 1st timer here, Well this is my question. I have a range of numbers in two columns, and I would like to be able to for instance say Im looking for a number between 500 - 480, when it locates the number to indicate it in the column next to it with a number 1 0r 2.
this is what im using but Im out in left field and at a loss. The $t$3 and $u$3 cells are the different number ranges that i can change to looking for.

=IF($T$3<=T8,IF($U$3>=U8,1,""))

526 454 answer
510 477 answer
500 477 answer

thanks


[ This Message was edited by: Wing-it on 2002-05-08 07:35 ]

[Ian Mac]

On 2002-05-08 07:31, Wing-it wrote:
hello folks, 1st timer here, Well this is my question. I have a range of numbers in two columns, and I would like to be able to for instance say Im looking for a number between 500 - 480, when it locates the number to indicate it in the column next to it with a number 1 0r 2.
this is what im using but Im out in left field and at a loss. The $t$3 and $u$3 cells are the different number ranges that i can change to looking for.

=IF($T$3<=T8,IF($U$3>=U8,1,""))

526 454 answer
510 477 answer
500 477 answer

thanks


[ This Message was edited by: Wing-it on 2002-05-08 07:35 ]

not sure what you mean by a 1 or 2

but this may be helpful

=IF(AND(T8<=$T$3,U8>=$U$3),1,"")

or alternatively:

=(T8<=$T$3)*(U8>=$U$3)*1

where the answer will be 1 or 0

any help?

[Wing-it]

Ian,

Thanks for the post, But that still is not getting the answer im looking for. Its pulling only half of the range, Not the range from one number to the other. like if i choose 530 - 490, it calls 541 - 482. Do you have another idea as to how I can pull the range im looking for.

Thanks again

[Ian Mac]

On 2002-05-08 08:22, Wing-it wrote:
Ian,

Thanks for the post, But that still is not getting the answer im looking for. Its pulling only half of the range, Not the range from one number to the other. like if i choose 530 - 490, it calls 541 - 482. Do you have another idea as to how I can pull the range im looking for.

Thanks again

I'm now entirely lost as to what you need.
could explain further what

like if i choose 530 - 490, it calls 541 - 482

means.
Where do these numbers come from?

[Wing-it]

Thickness Range
530 490


A End B End
max min max min
526 454 519 451
510 477 525 468
500 471 495 457
507 473 507 469
506 476 530 478
496 470 535 476
488 474 511 471
518 466 484 465
501 481 510 479
513 484 495 473
510 470 504 485
495 486 491 473
489 454 514 476
491 470 513 483
491 464 478 465
523 467 479 452
476 487 502 477
530 463 1 495 469
479 491 549 489
506 450 482 446
475 457 515 475
502 456 475 463


sort of a little data base, at the top in thickness range I can choose the range I'm looking for, in the column next to the number it will indicate which ones are in that range.

Sorry about that sometimes I have a hard time discribing things.

Wing-it
If it will help i could send to u

[ This Message was edited by: Wing-it on 2002-05-08 08:43 ]

[Aladin Akyurek]

On 2002-05-08 08:38, Wing-it wrote:

Thickness Range
530 490


A End B End
max min max min
526 454 519 451
510 477 525 468
500 471 495 457
507 473 507 469
506 476 530 478
496 470 535 476
488 474 511 471
518 466 484 465
501 481 510 479
513 484 495 473
510 470 504 485
495 486 491 473
489 454 514 476
491 470 513 483
491 464 478 465
523 467 479 452
476 487 502 477
530 463 1 495 469
479 491 549 489
506 450 482 446
475 457 515 475
502 456 475 463


sort of a little data base, at the top in thickness range I can choose the range I'm looking for, in the column next to the number it will indicate which ones are in that range.

Sorry about that sometimes I have a hard time discribing things.

Wing-it
If it will help i could send to u

[ This Message was edited by: Wing-it on 2002-05-08 08:43 ]

Is this a 2-coulmn or 4-column table?

If the latter, which rows contain the pair you want look for?

[Wing-it]

Ok this is a 4 column data base with about 1200 numbers in each column.

Example:

cell T3 and U3 are the cells I choose for the formula to search for. 500 480

Thickness Range
500 480


A End B End
max min max min
526 454 1 519 451 1
510 477 1 525 468 1
500 471 1 495 457
507 473 1 507 469 1
506 476 1 530 478 1
496 470 535 476 1
488 474 511 471 1
518 466 1 484 465
501 481 510 479 1
513 484 495 473

the result should indicate in the column to the right of the two data columns.

What im looking for, is if i indicate set of number between 500 - 470, it should indicated in the column next to the data which number falls in between the range I choose.

Well I can't seam to get the post to stay formated to show u, All i can say is there are two columns of data then the answer or the formula is in the column next to the two data columns the other two columns are a repeat of the same thing.

thanks

[ This Message was edited by: Wing-it on 2002-05-08 09:34 ]

[Aladin Akyurek]

On 2002-05-08 09:26, Wing-it wrote:
Ok this is a 4 column data base with about 1200 numbers in each column.

Example:

cell T3 and U3 are the cells I choose for the formula to search for. 500 480

Thickness Range
500 480


A End B End
max min max min
526 454 1 519 451 1
510 477 1 525 468 1
500 471 1 495 457
507 473 1 507 469 1
506 476 1 530 478 1
496 470 535 476 1
488 474 511 471 1
518 466 1 484 465
501 481 510 479 1
513 484 495 473

the result should indicate in the column to the right of the two data columns.

What im looking for, is if i indicate set of number between 500 - 470, it should indicated in the column next to the data which number falls in between the range I choose.

Well I can't seam to get the post to stay formated to show u, All i can say is there are two columns of data then the answer or the formula is in the column next to the two data columns the other two columns are a repeat of the same thing.

thanks [img]/board/images/smiles/icon_smile.gif[/img]

[ This Message was edited by: Wing-it on 2002-05-08 09:34 ]

I'll assume your database to start at row 3 from A3 on: that is, 526 (the first number) is in A3, 454 in B3, C3 is currently empty, 519 in D3, 451 in E3, and F3 is currently empty.

Apparantly, you want a forrmula in C3 that checks whether T3 is between A3 and B3, and another formula in F3 that checks whether U3 is between D3 and E3.

If so,

in C3 enter and copy down:

=($T$3>=B3)*($T$3<=A3)

in F3 enter and copy down:

=($U$3>=E3)*($U$3<=D3)

Hope this is what you are looking for.

[Wing-it]

well thats close, but I want the formula to be able to check the range I give 500 - 590, or any range, the formula needs to check both a & B column at the same time it see if it falls in between or is in the same range. Which looks for anything from 500 to 590 from each column

Thanks again

[Wing-it]

Please don't give up on me know!!!!