Results 1 to 7 of 7

Thread: creating variable results for a single cell

  1. #1

    creating variable results for a single cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #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"})
    Attached Files Attached Files
    Last edited by martindwilson; 2013-12-29 at 11:13 PM.

  3. #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. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,315
    Articles
    0
    Excel Version
    2010 on Xubuntu
    In this case,you might also try
    Code:
    =CHAR(65+FLOOR(A1/10,1))
    instead of LOOKUP

    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. #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. #6
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,315
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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. #7
    Thank you Percoflyer, that worked fine. I think I can do the ratios now, so much obliged for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •