+ Reply to Thread
Results 1 to 21 of 21

help with condition /need to make ranking/

  1. #1
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    help with condition /need to make ranking/

    in column L are Totals points
    in column K are Temporary points

    and i want to make rank according to points in L and K

    less than 2000 of total points = C1
    2000-6000 of total points = C2
    6000 and more of total points = C3
    BUT
    if have 3000+ temporary points and 18000+ total points = M1 until
    76 000+ temporary points = M2
    200 000+ temporary points = R1
    400 000 + temporay points = R2

    i try it like that
    =IF(AND(L1>1999,9;L1<6000);"C2";IF(L1>5999,9;"C3";IF(AND((K1>2999,99);(L1)>17999,99);"M1";IF(K1>75999,9;"M2";IF(K1>199999,9;"R1";IF(K1>399999,9;"R2";"C1"))))))

    but it works, but not correctly..for example if have 8606088,566 temporary and 220000 totals it write C3 but it is R2

    maybe it is only problem with parentheses or wrong true, false in if..i am clueless..please help me
    Last edited by nitr0; 08-11-2010 at 03:46 PM. Reason: bad topic name

  2. #2
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: cant deal with parentheses or what

    Hi can you send a short version of your file as an example

    Thanks
    If I helped, Don't forget to add to my reputation (click on the little scale)
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: cant deal with parentheses or what

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: cant deal with parentheses or what

    Sure, i work in 2007, but i uploaded also in compatibility mode
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: cant deal with parentheses or what

    Sure, i work in 2007, but i uploaded also in compatibility mode

    //sorry bad formula.. this is correct
    Attached Files Attached Files

  6. #6
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: cant deal with parentheses or what

    if have 3000+ temporary points and 18000+ total points = M1 until
    I didn't get this part! Should it be greater, equal or less than something?

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: cant deal with parentheses or what

    You both need to read & follow the Forum Rules if you wish to continue using the Forum.

  8. #8
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: cant deal with parentheses or what

    Quote Originally Posted by meyero90 View Post
    I didn't get this part! Should it be greater, equal or less than something?
    c1,c2 and c3 have only one factor (total points)
    M1 have two factors (total and temporary points)
    M2, R1, R2 have only one factor again (temporary points)

    so it mean that man will have rank C3 as long as it complete 2 condition
    for example it can have 18000 total points (which is adequate for M1) but have only 2500 of temporary points (need 3000) so man is only C3 until he will have 3000 temp.points

    and M2,R1, R2 depend only on temporary points..so man can have 1000 0000 totals points and only 70 000 and be M1, 78 000 temp and for ex. 0 total = M2
    Last edited by nitr0; 08-10-2010 at 01:29 PM.

  9. #9
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: cant deal with parentheses or what

    I don't understand why I am concerned by your above post royUK.....I am just replying to his post....is that wrong? Or may be because the post title is incorrect makes me also liable for this?

    Apologies but I quite don't get it
    Last edited by meyero90; 08-10-2010 at 01:23 PM. Reason: typo

  10. #10
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    I try it like this..

    this is rank table:
    <2000 total points - C1
    2000 total points - C2
    6000 total points - C3

    18 000 total points and
    3000 temp points
    => M1

    76 000 temp points - M2
    200 000 temp points - R1
    400 000 temp points - R2

    So Man who have nothing in temp points can be only c1,c2 or c3
    who have something in both can be everything
    and who have only in temp can be M2, R1, R2 according to condition above

  11. #11
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: help with condition /need to make ranking/

    Sorry man, I made many attempts but couldn't solve it. I'll keep you posted when I find the correct answer.

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: help with condition /need to make ranking/

    Try this

    =IF(L2=0,"",IF(L2<2000,"C1",IF(L2<6000,"C2",IF(L2>3000,IF(M2>400000,"R2",IF(M2>200000,"R1",IF(M2>76000,"M2",IF(M2>18000,"M1","C3")))),"C3"))))

    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  13. #13
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    no it doesnt work, because for example

    L2 = 20
    M2 = 77 000
    return C1 and should be M2

    it means when it come to first condition and it is true, return value, it not going deeper to next condition..it stop when it is satisfied with return value

  14. #14
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: help with condition /need to make ranking/

    Hey nitro,

    I quite don't get actually. your formula mentions an L2, but L2 is an empty cell in your spreadsheet? did I download the wrong one?

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: help with condition /need to make ranking/

    I thought, based on the way you had it written that for the M's and R's, L2 had to be >3000. Is that only for M1?

  16. #16
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    no, you are right, it is empty, but ChemistB post me formula with L, M cells, so type value in it.

  17. #17
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    Quote Originally Posted by ChemistB View Post
    I thought, based on the way you had it written that for the M's and R's, L2 had to be >3000. Is that only for M1?
    yes..it is only for M1,..only M1 depends on 2 values, other only on one..

  18. #18
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    is it possible to make it like that? because i am clueless..

  19. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: help with condition /need to make ranking/

    Okay, try this

    =IF(AND(L1>=18000,K1>=3000),"M1",IF(K1<76000, IF(L1<2000,"C1",IF(L1<6000,"C2","C3")),IF(K1<200000,"M2", IF(K1< 400000, "R1", IF(K1>=400000,"R2","C3")))))
    That works for me (based on how I'm reading your criteria)

  20. #20
    Registered User
    Join Date
    07-22-2010
    Location
    Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: help with condition /need to make ranking/

    This is exactly what I need..thank you very very much..

    How can add you reputation? clicking on little green square doesnt work
    //ok..i sent it
    Last edited by nitr0; 08-11-2010 at 03:47 PM.

  21. #21
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: help with condition /need to make ranking/

    Glad we got that figured out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1