Is there a better way?

[Ymtrader]

The only way I know how to do this is with a really ugly nested IF
statement. Is there a better way?

I have 8 "zones" of numbers and I need to determine which zone this
number fits into.

zn1 zn2 zn3
zn4 zn5 zn6 zn7
zn8
1410 1416 1416 1421 1421 1423 1423 1436 1436 1437 1437
1439 1439 1445 1445 1450

If I need to know what zone number 1422 fits into I can only to think
of a nested IF statement:

IF(P2>G2,IF(P2<H2,"ZN3")..................................................................................................................)

I am thinking there must be an easier more efficient way. Any ideas?
Thanks!

 

[Don Guillett Excel MVP]

The only way I know how to do this is with a really ugly nested IF
statement.  Is there a better way?

I have 8 "zones" of numbers and I need to determine which zone this
number fits into.

      zn1               zn2                 zn3
zn4           zn5              zn6          zn7
zn8
1410  1416       1416  1421    1421 1423  1423 1436  1436 1437  1437
1439  1439 1445  1445 1450

If I need to know what zone number 1422 fits into I can only to think
of a nested IF statement:

IF(P2>G2,IF(P2<H2,"ZN3")....................................................­...............................................................)

I am thinking there must be an easier more efficient way.  Any ideas?
Thanks!

Har zn1 zn2 zn3
zn4 zn5 zn6 zn7
zn8
1410 1416 1416 1421 1421 1423 1423 1436 1436 1437 1437
1439 1439 1445 1445 1450

Hard to tell from your broken paste
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Martin Brown]

It looks like what you want is some variant of HLOOKUP - minor syntax
restriction is that it requires the lookup to be on the first row and
for a nearest match to work the first line must be in ascending order.

G: 1 4 10 20
H: zn1 zn2 zn3 zn4

Assuming it starts in rows G, H from column1 then

=HLOOKUP(P2, $G$1..$H$8,2,1)

ought to do what you want.

Regards,
Martin Brown

 

[Pete_UK]

Your numbers seem to define a range, although you only need the lower
number of each range, like this:

zn1 zn2 zn3 zn4 zn5 zn6 zn7 zn8
1410 1416 1421 1423 1436 1437 1439 1445

Assume these occupy the top two rows of your sheet, beginning in A1,
and that the number to be tested is in A4. You can put this formula in
B4:

=IF(A4<A2,"too small",IF(A4>1450,"too
large",INDEX(A1:H1,MATCH(A4,A2:H2))))

This will return the zone number as long as A4 is in range, and will
give error messages if it is not.

Hope this helps.

Pete

 

[Rick Rothstein]

Two questions...

1. Are the two values making up a single zone in the same cell or in two
different cells?

2. You need to tell us what is supposed to happen with numbers like 1416
which fall on the boundary of two adjacent zones (that is, which zone does
it belong to)?

It would help us if you told us the columns making up each zone so we can
see how to address your numbers.

 

[Ymtrader]

Two questions...

1. Are the two values making up a single zone in the same cell or in two
different cells?

2. You need to tell us what is supposed to happen with numbers like 1416
which fall on the boundary of two adjacent zones (that is, which zone does
it belong to)?

It would help us if you told us the columns making up each zone so we can
see how to address your numbers.

Rick, thank you I see now that this was not clear. The numbers on the
sheet are not as I posted. I posted as a way to explain but in turn
gave the wrong information for the needed solution, the layout is as
such: Each number is in it's own cell

a1:1410 b1:1416 c1:1421 d1:1423 e1:1436 f1:1437 g1:1439 h1:1445
i1:1450 - So a number that falls between the 2 adjecent numbers will
define the zone. ie a number of 1415 would be in zone 1 a number of
1448 would be in zone 8. Zone 1 would be greater or equal to 1410
but less than 1416 Zone 2 would be greater or equal to 1416 but less
than 1421 ect. Sorry for the miss communication.

 

[joeu2004]

a1:1410 b1:1416 c1:1421 d1:1423 e1:1436 f1:1437 g1:1439 h1:1445
i1:1450 -  So a number that falls between the 2 adjecent numbers will
define the zone.  ie a number of 1415 would be in zone 1 a number of
1448 would be in zone 8.   Zone 1 would be greater or equal to 1410
but less than 1416   Zone 2 would be greater or equal to 1416 but less
than 1421 ect.

=MATCH(A2,A1:I1)

gives the zone number. If you want text:

="ZN" & MATCH(A2:A1:I1)


PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[Ymtrader]

=MATCH(A2,A1:I1)

gives the zone number.  If you want text:

="ZN" & MATCH(A2:A1:I1)

PS:  For broader participation, you might want to post future
inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Thank you all for your help, I will try each of the suggestions.
Again thank you!