Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

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 -


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

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 -




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

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 -


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

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 -


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help

On Apr 16, 4:46*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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}),1 3,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(AN*D(--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 -- Hide quoted text -


- Show quoted text -


  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for you.

Rick


wrote in message
...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help

On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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}),1 3,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(AN*D(--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 -- Hide quoted text -


- Show quoted text -


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39*am, "Rick Rothstein \(MVP - VB\)"
wrote:
It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for you.

Rick

wrote in message

...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help

On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"



wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

I'm not sure what to tell you... I tested the formula before I posted it and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell? If
you still can't get it to work, describe exactly what you have and what you
are doing so we can try to duplicate your conditions (and your error).

Rick


wrote in message
...
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"
wrote:
It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for you.

Rick

wrote in message

...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help

On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"



wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is


On Apr 18, 3:50*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I'm not sure what to tell you... I tested the formula before I posted it and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell? If
you still can't get it to work, describe exactly what you have and what you
are doing so we can try to duplicate your conditions (and your error).

Rick

wrote in message

...
there was an error with the formula, i think it said lookup vector not
specified.

On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"



wrote:
It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

I just sent you a sample worksheet.

Rick


wrote in message
...
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is


On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell? If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).

Rick

wrote in message

...
there was an error with the formula, i think it said lookup vector not
specified.

On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"



wrote:
It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

its workin correctly now. i would just need to add more irrelevant
number that equal a number set bettween 1 and 24. can i just copy and
paste parts of the formula to use the same concept so i can add on.

On Apr 21, 11:23*am, "Rick Rothstein \(MVP - VB\)"
wrote:
I just sent you a sample worksheet.

Rick

wrote in message

...
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is

On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"



wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell? If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).


Rick


wrote in message


...
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"


wrote:
It's a big formula because you have a lot of out-of-sequence ranges that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


....
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default number set input, specific number output

I don't know... it depends on what those modification are and how many more
of them you have.

Rick


wrote in message
...
its workin correctly now. i would just need to add more irrelevant
number that equal a number set bettween 1 and 24. can i just copy and
paste parts of the formula to use the same concept so i can add on.

On Apr 21, 11:23 am, "Rick Rothstein \(MVP - VB\)"
wrote:
I just sent you a sample worksheet.

Rick

wrote in message

...
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is

On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"



wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell?
If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).


Rick


wrote in message


...
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"


wrote:
It's a big formula because you have a lot of out-of-sequence ranges
that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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}),1 3,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*(AN*D(--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)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -




  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

ill get all of them together and ill post them so u can see them. ill
list them in order

On Apr 21, 12:36*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I don't know... it depends on what those modification are and how many more
of them you have.

Rick

wrote in message

...
its workin correctly now. i would just need to add more irrelevant
number that equal a number set bettween 1 and 24. can i just copy and
paste parts of the formula to use the same concept so i can add on.

On Apr 21, 11:23 am, "Rick Rothstein \(MVP - VB\)"



wrote:
I just sent you a sample worksheet.


Rick


wrote in message


...
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is


On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell?
If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).


Rick


wrote in message


....
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"


wrote:
It's a big formula because you have a lot of out-of-sequence ranges
that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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(O*R*(*-*-*MID(B1,3,3)={19,21,22,24,25,26,27,28,29,30,35}),1 3,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*(AN*D(--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((--M*ID(B1*,3,3)*-1)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

alright here it is in full

1= 36,44,45,46
3= 37,38,39,54,55
5= 40-43
6= 47,48,49, 146-159, 201-210
7= 23, 83,84, 211
10= 212,214, 227
11= 57-82, 87-136, 163,167, 199, 213
12= 31-34
13= 21,22, 24-30, 160-197
14= 85,86
15= 139-141
16= 1,2,13,14,17,18,53
17= 3,4,15,16,20
19= 50,51,52
20= 19
22= 142-145

thnx for ur help. i really appreciate it


On Apr 21, 1:39*pm, wrote:
ill get all of them together and ill post them so u can see them. ill
list them in order

On Apr 21, 12:36*pm, "Rick Rothstein \(MVP - VB\)"



wrote:
I don't know... it depends on what those modification are and how many more
of them you have.


Rick


wrote in message


...
its workin correctly now. i would just need to add more irrelevant
number that equal a number set bettween 1 and 24. can i just copy and
paste parts of the formula to use the same concept so i can add on.


On Apr 21, 11:23 am, "Rick Rothstein \(MVP - VB\)"


wrote:
I just sent you a sample worksheet.


Rick


wrote in message


....
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is


On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell?
If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).


Rick


wrote in message


...
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"


wrote:
It's a big formula because you have a lot of out-of-sequence ranges
that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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(*O*R*(*-*-*MID(B1,3,3)={19,21,22,24,25,26,27,28,29,30,35}),1 3,IF(OR(AND(--MI*D(*B1*,3*,3*)*145,--MID(B1,3,3)<160),AND(--MID(B1,3,3)200,--MID(B1,3,3)<2*08)*),6*,IF*(OR*(AN*D(--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*((--M*ID(B1*,3,3)*-1)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default number set input, specific number output

bump bump bump

On Apr 23, 1:35*pm, wrote:
alright here it is in full

1= 36,44,45,46
3= 37,38,39,54,55
5= 40-43
6= 47,48,49, 146-159, 201-210
7= 23, 83,84, 211
10= 212,214, 227
11= 57-82, 87-136, 163,167, 199, 213
12= 31-34
13= 21,22, 24-30, 160-197
14= 85,86
15= 139-141
16= 1,2,13,14,17,18,53
17= 3,4,15,16,20
19= 50,51,52
20= 19
22= 142-145

thnx for ur help. i really appreciate it

On Apr 21, 1:39*pm, wrote:



ill get all of them together and ill post them so u can see them. ill
list them in order


On Apr 21, 12:36*pm, "Rick Rothstein \(MVP - VB\)"


wrote:
I don't know... it depends on what those modification are and how many more
of them you have.


Rick


wrote in message


....
its workin correctly now. i would just need to add more irrelevant
number that equal a number set bettween 1 and 24. can i just copy and
paste parts of the formula to use the same concept so i can add on.


On Apr 21, 11:23 am, "Rick Rothstein \(MVP - VB\)"


wrote:
I just sent you a sample worksheet.


Rick


wrote in message


....
could u send me a copy of the excel file where you tested it and got
it to work. i tried duplicating, as in putting a number pp between 1
and 226 on column b and copied the the formula to coulmn a and still
didnt work correctly.
thnx for ur help
my email is


On Apr 18, 3:50 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
I'm not sure what to tell you... I tested the formula before I posted it
and
I just tested it again... it works fine. If you put a number in B1 that
starts with "pp", it generates a number from your listing. Did you put a
value in B1? Or do you need to change B1 to a different starting cell?
If
you still can't get it to work, describe exactly what you have and what
you
are doing so we can try to duplicate your conditions (and your error).


Rick


wrote in message


...
there was an error with the formula, i think it said lookup vector not
specified.


On Apr 17, 1:39 am, "Rick Rothstein \(MVP - VB\)"


wrote:
It's a big formula because you have a lot of out-of-sequence ranges
that
require special handling. Definitely let us know how it works out for
you.


Rick


wrote in message


...
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help


On Apr 16, 4:46 pm, "Rick Rothstein \(MVP - VB\)"


wrote:
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*(*O*R*(*-*-*MID(B1,3,3)={19,21,22,24,25,26,27,28,29,30,35}),1 3,IF(OR(AND(--*MI*D(*B1*,3*,3*)*145,--MID(B1,3,3)<160),AND(--MID(B1,3,3)200,--MID(B1,3,3*)<2*08)*),6*,IF*(OR*(AN*D(--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*((--M*ID(B1*,3,3)*-1)/1*0))))*))


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 -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I was wondering how to have excel have a specific number input Jiffy Excel Discussion (Misc queries) 1 May 10th 10 10:23 AM
inverse of the column function? i.e. input a number, output thecorresponding column text label Brotherharry Excel Worksheet Functions 19 February 14th 09 01:37 AM
How to calculate number of occurencies of a specific number number Stefan Excel Discussion (Misc queries) 4 September 8th 08 08:33 AM
find an exact number in a different sheet and use the cell containing the number to output information [email protected] Excel Programming 4 February 18th 07 09:59 PM
making a cell fixed number to a input number fwday[_2_] Excel Programming 1 October 25th 03 02:38 AM


All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"