Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A range of values transformed
I want to make a rule that if a cell has a range of
values, it is coded a certain way. How would I do this this? For example, I have a row of values 5, 79, 21, 0 With the key of 0 = 1 1-10 = 2 11-25 = 3 26-75 = 4 75 = 5 Leading to: 2,5,3,1 How would I write this with a simple excel program? I was thinking of combining if statements, but the formula gets too large. PLEASE HELP! Thank you! Alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A range of values transformed
Alex
try this: =IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND(A7=11,A7 <=25),3,IF(AND(A7=26,A7<= 75),4,5)))) This tests cell A7; modify to your requirements. Regards Trevor "Alex" wrote in message ... I want to make a rule that if a cell has a range of values, it is coded a certain way. How would I do this this? For example, I have a row of values 5, 79, 21, 0 With the key of 0 = 1 1-10 = 2 11-25 = 3 26-75 = 4 75 = 5 Leading to: 2,5,3,1 How would I write this with a simple excel program? I was thinking of combining if statements, but the formula gets too large. PLEASE HELP! Thank you! Alex |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!
I don't think this works because I tried it and all I got
back was 2's. -----Original Message----- Alex try this: =IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND (A7=11,A7<=25),3,IF(AND(A7=26,A7<= 75),4,5)))) This tests cell A7; modify to your requirements. Regards Trevor "Alex" wrote in message ... I want to make a rule that if a cell has a range of values, it is coded a certain way. How would I do this this? For example, I have a row of values 5, 79, 21, 0 With the key of 0 = 1 1-10 = 2 11-25 = 3 26-75 = 4 75 = 5 Leading to: 2,5,3,1 How would I write this with a simple excel program? I was thinking of combining if statements, but the formula gets too large. PLEASE HELP! Thank you! Alex . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!
Alex
it works fine ... Put the numbers from 0 to 99 in cells A1 to A100. Then put the formula in cell B1 and drag down. =IF(A1=0,1,IF(AND(A1=1,A1<=10),2,IF(AND(A1=11,A1 <=25),3,IF(AND(A1=26,A1<= 75),4,5)))) 0 1 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 11 3 12 3 13 3 14 3 15 3 16 3 17 3 18 3 19 3 20 3 21 3 22 3 23 3 24 3 25 3 26 4 27 4 28 4 29 4 30 4 31 4 32 4 33 4 34 4 35 4 36 4 37 4 38 4 39 4 40 4 41 4 42 4 43 4 44 4 45 4 46 4 47 4 48 4 49 4 50 4 51 4 52 4 53 4 54 4 55 4 56 4 57 4 58 4 59 4 60 4 61 4 62 4 63 4 64 4 65 4 66 4 67 4 68 4 69 4 70 4 71 4 72 4 73 4 74 4 75 4 76 5 77 5 78 5 79 5 80 5 81 5 82 5 83 5 84 5 85 5 86 5 87 5 88 5 89 5 90 5 91 5 92 5 93 5 94 5 95 5 96 5 97 5 98 5 99 5 Have you copied and pasted the formula and adjusted it or retyped it ? Regards Trevor "Alex" wrote in message ... I don't think this works because I tried it and all I got back was 2's. -----Original Message----- Alex try this: =IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND (A7=11,A7<=25),3,IF(AND(A7=26,A7<= 75),4,5)))) This tests cell A7; modify to your requirements. Regards Trevor "Alex" wrote in message ... I want to make a rule that if a cell has a range of values, it is coded a certain way. How would I do this this? For example, I have a row of values 5, 79, 21, 0 With the key of 0 = 1 1-10 = 2 11-25 = 3 26-75 = 4 75 = 5 Leading to: 2,5,3,1 How would I write this with a simple excel program? I was thinking of combining if statements, but the formula gets too large. PLEASE HELP! Thank you! Alex . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting "question mark in block" at EOL in CSV files transformed fromXML | Excel Discussion (Misc queries) | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions |