Help with if formula based on values
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Help with if formula based on values

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Help with if formula based on values

     
    Hi Guys need help with below formula as per image current formula is looking into ranking based on A2 which is fine but if I get duplicate value in column AM like line 5 and 6 I want it to give the same ranking in this case it would be 4 and 4

    Thank you

    Image:

    https://imgur.com/a/WPaTs

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,929
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    98 1 98 1
    90 2 90 2 ####
    60 3 60 3
    55 4 55 4
    55 5 55 4
    46 6 46 6
    33 7 33 7
    20 8 20 8
    using large function, then looking for duplications
    and if found use the rank of the first one
    ####
    =IF(G2=G1,F1,F2)

  3. #3
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    2,791
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    You're not showing what formula is there at the moment but perhaps for AM2:

    Code:
    =SUMPRODUCT(($A:$A=$A2)*($AL:$AL>$AL2))+1
    It would be better to restrict $A:$A and $AL:$AL to include only the populated data (e.g. $A$2:$A$1000) as these will be slow when they're scanning the whole column.

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  4. #4
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    current formula:


    https://imgur.com/a/lB1rA

  5. #5
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    Thank you for reply I need to show instead of 6 as ranked as 5th after 4, 4

  6. #6
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    Quote Originally Posted by oldbrewer View Post
    98 1 98 1
    90 2 90 2 ####
    60 3 60 3
    55 4 55 4
    55 5 55 4
    46 6 46 6
    33 7 33 7
    20 8 20 8
    using large function, then looking for duplications
    and if found use the rank of the first one
    ####
    =IF(G2=G1,F1,F2)
    Thank you for reply I need to show instead of 6 as ranked as 5th after 4, 4

  7. #7
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    2,791
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    Ah. So they're currently sorted anyway. In which case in AM2:

    Code:
    =IF(A1=A2,IF(AL1=AL2,AM1,AM1+1),1)
    That will give you 1,2,2,3,3,4,4,5,6,7,8 in your data as shown. Is that what you want? I you want 1,2,2,4,4,6,6,8,9,10,11 then it's a little more tricky.

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  8. #8
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    Quote Originally Posted by wideboydixon View Post
    You're not showing what formula is there at the moment but perhaps for AM2:

    Code:
    =SUMPRODUCT(($A:$A=$A2)*($AL:$AL>$AL2))+1
    It would be better to restrict $A:$A and $AL:$AL to include only the populated data (e.g. $A$2:$A$1000) as these will be slow when they're scanning the whole column.

    WBD
    its just crashed my exel

  9. #9
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    2,791
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

    Quote Originally Posted by kypok999 View Post
    its just crashed my exel
    Boom! Try the alternative formula then

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  10. #10
    New Member
    Join Date
    Oct 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with if formula based on values

      
    Quote Originally Posted by wideboydixon View Post
    Ah. So they're currently sorted anyway. In which case in AM2:

    Code:
    =IF(A1=A2,IF(AL1=AL2,AM1,AM1+1),1)
    That will give you 1,2,2,3,3,4,4,5,6,7,8 in your data as shown. Is that what you want? I you want 1,2,2,4,4,6,6,8,9,10,11 then it's a little more tricky.

    WBD
    Hi WBD I need following as currently its 1,2,2,4,4,6,6,8,9,10,11 but I want 1,2,2,3,3,4,4,5,6,7,8

    https://imgur.com/a/RAJ5p
    Last edited by kypok999; Feb 13th, 2018 at 11:40 AM.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com