| Home |
| Search |
| Today's Posts |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I was wondering how to have excel have a specific number input | Excel Discussion (Misc queries) | |||
| inverse of the column function? i.e. input a number, output thecorresponding column text label | Excel Worksheet Functions | |||
| How to calculate number of occurencies of a specific number number | Excel Discussion (Misc queries) | |||
| find an exact number in a different sheet and use the cell containing the number to output information | Excel Programming | |||
| making a cell fixed number to a input number | Excel Programming | |||
Threaded Mode
