i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right

Need a bit more information, where are you inputting a number?

--

Regards,
Nigel




wrote in message
...
i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right

If I understand what you want correctly, put this formula in A1 and copy it
down as far as you need to...

=IF(OR(B1="",B1200),"",1+INT((B1-1)/10))

If you put a number between 1 and 200 in Column B, the value displayed in
Column A will be in accordance with the chart you posted. You didn't mention
what to do if a number greater than 200 is entered in Column B... the above
formula will display a blank cell in that case.

Rick


wrote in message
...
i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right

i went back in to look at my data and its going to be a little more
difficult than that. for example i would have to change several
things. lets say that 214 - 226 that has to be a 10 and 201 -207 has
to be a 6
there are number that dont go in order they jus have to be a specific
number
and the information im getting from with the numbers betweeen 1 and
230 are on a another sheet on the same file with the two letters in
front of it. for example
pp12
pp35
pp100
and so on

On Apr 16, 2:49*am, "Rick Rothstein \(MVP - VB\)"
wrote:
If I understand what you want correctly, put this formula in A1 and copy it
down as far as you need to...

=IF(OR(B1="",B1200),"",1+INT((B1-1)/10))

If you put a number between 1 and 200 in Column B, the value displayed in
Column A will be in accordance with the chart you posted. You didn't mention
what to do if a number greater than 200 is entered in Column B... the above
formula will display a blank cell in that case.

Rick

wrote in message

...



i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 * *column 2
1 * * * * * * * *1-10
2 * * * * * * * *11-20
3 * * * * * * * *21-30
4 * * * * * * * *31-40
5 * * * * * * * *41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right- Hide quoted text -

- Show quoted text -

I don't think I am following what you are saying completely. Please answer
the following questions:

1) Do ALL your "numbers" have letters in front of them or only some of them?

2) For those having letters in front of them, are those letters always "pp"
as you showed in your example? If not, do you know what those letters are or
will ever be?

3) You gave an example of number mapping like 214-226 having to become 10...
this is more than the 200 you originally said was the maximum possible
number... do you actually now have to handle numbers larger than 200?

4) If the answer to #3 is that you have to handle numbers larger than 200,
and if the mappings are no longer linear after 200 (that seems to be what
your examples are showing), can you list for us ALL the non-linear ranges
and what they map to?

Rick


wrote in message
...
i went back in to look at my data and its going to be a little more
difficult than that. for example i would have to change several
things. lets say that 214 - 226 that has to be a 10 and 201 -207 has
to be a 6
there are number that dont go in order they jus have to be a specific
number
and the information im getting from with the numbers betweeen 1 and
230 are on a another sheet on the same file with the two letters in
front of it. for example
pp12
pp35
pp100
and so on

On Apr 16, 2:49 am, "Rick Rothstein \(MVP - VB\)"
wrote:
If I understand what you want correctly, put this formula in A1 and copy
it
down as far as you need to...

=IF(OR(B1="",B1200),"",1+INT((B1-1)/10))

If you put a number between 1 and 200 in Column B, the value displayed in
Column A will be in accordance with the chart you posted. You didn't
mention
what to do if a number greater than 200 is entered in Column B... the
above
formula will display a blank cell in that case.

Rick

wrote in message

...



i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right- Hide quoted text -

- Show quoted text -

1. all of them have the same letters pp in front of them

3. there are a total of 228 that can be input
for output up to 24

146-159 and 201-207 = 6
214-226 = 10
63-78 and 87-91 and 97-135 = 11
31-34 = 12
19 21-22 24-30 and 35 = 13
85-86 = 14
139-141 = 15

i hope this helps. and thnx for all of ur help also.

On Apr 16, 9:24*am, "Rick Rothstein \(MVP - VB\)"
wrote:
I don't think I am following what you are saying completely. Please answer
the following questions:

1) Do ALL your "numbers" have letters in front of them or only some of them?

2) For those having letters in front of them, are those letters always "pp"
as you showed in your example? If not, do you know what those letters are or
will ever be?

3) You gave an example of number mapping like 214-226 having to become 10....
this is more than the 200 you originally said was the maximum possible
number... do you actually now have to handle numbers larger than 200?

4) If the answer to #3 is that you have to handle numbers larger than 200,
and if the mappings are no longer linear after 200 (that seems to be what
your examples are showing), can you list for us ALL the non-linear ranges
and what they map to?

Rick

wrote in message

...
i went back in to look at my data and its going to be a little more
difficult than that. for example i would have to change several
things. lets say that 214 - 226 that has to be a 10 and 201 -207 has
to be a 6
there are number that dont go in order they jus have to be a specific
number
and the information im getting from with the numbers betweeen 1 and
230 are on a another sheet on the same file with the two letters in
front of it. for example
pp12
pp35
pp100
and so on

On Apr 16, 2:49 am, "Rick Rothstein \(MVP - VB\)"



wrote:
If I understand what you want correctly, put this formula in A1 and copy
it
down as far as you need to...

=IF(OR(B1="",B1200),"",1+INT((B1-1)/10))

If you put a number between 1 and 200 in Column B, the value displayed in
Column A will be in accordance with the chart you posted. You didn't
mention
what to do if a number greater than 200 is entered in Column B... the
above
formula will display a blank cell in that case.

Rick

wrote in message

...

i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You could try this formula...

=IF(OR(--MID(B1,3,3)={31,32,33,34,85,86,139,140,141}),LOOKU P(--MID(B1,3,1),{1,3,8},{15,12,14}),IF(AND(--MID(B1,3,3)213,--MID(B1,3,3)<227),10,IF(OR(--MID(B1,3,3)={19,21,22,24,25,26,27,28,29,30,35}),13 ,IF(OR(AND(--MID(B1,3,3)145,--MID(B1,3,3)<160),AND(--MID(B1,3,3)200,--MID(B1,3,3)<208)),6,IF(OR(AND(--MID(B1,3,3)62,--MID(B1,3,3)<79),AND(--MID(B1,3,3)86,--MID(B1,3,3)<92),AND(--MID(B1,3,3)96,--MID(B1,3,3)<136)),11,1+INT((--MID(B1,3,3)-1)/10))))))

Rick


wrote in message
...
1. all of them have the same letters pp in front of them

3. there are a total of 228 that can be input
for output up to 24

146-159 and 201-207 = 6
214-226 = 10
63-78 and 87-91 and 97-135 = 11
31-34 = 12
19 21-22 24-30 and 35 = 13
85-86 = 14
139-141 = 15

i hope this helps. and thnx for all of ur help also.

On Apr 16, 9:24 am, "Rick Rothstein \(MVP - VB\)"
wrote:
I don't think I am following what you are saying completely. Please answer
the following questions:

1) Do ALL your "numbers" have letters in front of them or only some of
them?

2) For those having letters in front of them, are those letters always
"pp"
as you showed in your example? If not, do you know what those letters are
or
will ever be?

3) You gave an example of number mapping like 214-226 having to become
10...
this is more than the 200 you originally said was the maximum possible
number... do you actually now have to handle numbers larger than 200?

4) If the answer to #3 is that you have to handle numbers larger than 200,
and if the mappings are no longer linear after 200 (that seems to be what
your examples are showing), can you list for us ALL the non-linear ranges
and what they map to?

Rick

wrote in message

...
i went back in to look at my data and its going to be a little more
difficult than that. for example i would have to change several
things. lets say that 214 - 226 that has to be a 10 and 201 -207 has
to be a 6
there are number that dont go in order they jus have to be a specific
number
and the information im getting from with the numbers betweeen 1 and
230 are on a another sheet on the same file with the two letters in
front of it. for example
pp12
pp35
pp100
and so on

On Apr 16, 2:49 am, "Rick Rothstein \(MVP - VB\)"



wrote:
If I understand what you want correctly, put this formula in A1 and copy
it
down as far as you need to...

=IF(OR(B1="",B1200),"",1+INT((B1-1)/10))

If you put a number between 1 and 200 in Column B, the value displayed
in
Column A will be in accordance with the chart you posted. You didn't
mention
what to do if a number greater than 200 is entered in Column B... the
above
formula will display a blank cell in that case.

Rick

wrote in message

...

i have 2 columns one column can be a number between 1 and 200,
depending on the number inserted there has to be a number between 1
and 20 outputted on the left

column 1 column 2
1 1-10
2 11-20
3 21-30
4 31-40
5 41-50

and so on if a number between 1 and 10 is inputted a 1 should appear
to the right- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -