# Thread: creating variable results for a single cell

1. ## creating variable results for a single cell

I have six columns where a single cell could have a value between 1 and 60. I wish to classify the result as A, B, C, D, E, or F in another column as follows:
A =between 1 & 10, B = between 11 & 20, C =Between 21 & 30, D = Between 31&40, E=between 41 & 50, F= between 51 & 60.

As an example let's assume in my 6 columns I have the following results;

• 8 18 27 36 44 58 the resulting analysis would be A B C D E F
• 2 9 19 22 37 41 the resulting analysis would be A A B C D E

Using the above description can someone help me compile a formula that would categorize my column 1-60 results into the resulting letter values as shown in the 2 examples. Thanks in advance.

2. in separate cells then in g1 filled acros to l1
=LOOKUP(A1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})
in one cell in same order as numbers
=LOOKUP(\$A1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(\$B1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(\$C1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(\$D1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(\$E1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(\$F1,{1,11,21,31,41,51},{"A","B","C","D","E","F"})
in one cell sorted
=LOOKUP(SMALL(\$A1:\$F1,1),{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(SMALL(\$A1:\$F1,2),{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(SMALL(\$A1:\$F1,3),{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(SMALL(\$A1:\$F1,4),{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(SMALL(\$A1:\$F1,5),{1,11,21,31,41,51},{"A","B","C","D","E","F"})&" "&LOOKUP(SMALL(\$A1:\$F1,6),{1,11,21,31,41,51},{"A","B","C","D","E","F"})

3. Many thanks indeed. Phew I would never have solved that. All I need now is to calculate how many A's B's D's etc in each of the 6 columns as a total and as a percentage. Your help on that would be invaluable.
Tks

4. In this case,you might also try
Code:
`=CHAR(65+FLOOR(A1/10,1))`

To count the letters per column try
Code:
`=count(h\$1:h\$3,"a")`
and drag right

Average per column would be
Code:
`=count(h\$1:h\$3,"a")/counta(h\$1:h\$3)`

5. Regret I could not make this work.
Let's assume we have the following results;
 A A A A A A C C D D E E A A B C C D A A B C D D C C D D E F A A B D D D A C C D E F A B C C C C B B B D E E A C E E F F B C C D E F

The first column would have 7 A's, 2 B's, and 2 C's

I could not get your formula to yield that result

Sorry maybe I did not understand your method.

6. If your first column is col A the number of A's is given by =countif(a1:a20,"a") and the average =countif(a1:a20,"a")/counta(a1:a20) ; Adapt the range to your needs. The same goes for other letters

7. Thank you Percoflyer, that worked fine. I think I can do the ratios now, so much obliged for your help.