creating variable results for a single cell

[induna]

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.

 

[martindwilson]

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"})

 

[induna]

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

 

[Pecoflyer]

In this case,you might also try

=CHAR(65+FLOOR(A1/10,1))

instead of LOOKUP

To count the letters per column try

=count(h$1:h$3,"a")

and drag right

Average per column would be

=count(h$1:h$3,"a")/counta(h$1:h$3)

 

[induna]

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.

 

[Pecoflyer]

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

 

[induna]

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