number set input, specific number output

  • Thread starter Thread starter jzamilpa3
  • Start date Start date
J

jzamilpa3

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="",B1>200),"",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
 
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
 
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


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
 
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.
 
You could try this formula...

=IF(OR(--MID(B1,3,3)={31,32,33,34,85,86,139,140,141}),LOOKUP(--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


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.
 
thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help
 
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


thats a big formula. but ill try it out and let u know how it works
out.
thnx for ur help
 
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


there was an error with the formula, i think it said lookup vector not
specified.
 
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 (e-mail address removed)
 
I just sent you a sample worksheet.

Rick


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 (e-mail address removed)
 
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.
 
I don't know... it depends on what those modification are and how many more
of them you have.

Rick


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.
 
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
 
bump bump bump

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




- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number Match Formula? 7
Six columns randon no.'s no dupes 6
Modify working code to expand the output. 5
If Multiple Criteria 1
Record Click on Error Indicator 2
Finding a Value Between Two Numbers and Returning Corresponding Value 10
specific number input, specific number output 1
Excel Help with dates 2

Back
Top