Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Formula needed

  1. #1

    Formula needed



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

    Hi, please check out my spreadsheet. I appreciate it if any one of you geniuses can help me out as I cant figure how to do this


    Download from the above link.....Sheet1 is what I am interested in having the formula do


    It would look up all the divided values and find if any are within 0.2 of each other and highlight the matches different colours from other matches.

    Thankyou all in advance

  2. #2
    apparently I need

  3. #3

  4. #4
    more posts

  5. #5
    to post my attachment

  6. #6

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you attach your file in the forum directly using the forum tools (Go Advanced - paperclip icon) or through a non-subscription site like dropbox or skydrive.


  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    need to remove /showdownload.php from middle of OP's link to get file.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Thanks NoS

    wowsers, please try selecting D3:X27 and apply conditional formatting using "use a formula to determine which cells to format", then insert formula:

    =AND(D3<>"",COUNTIFS($D$3:$X$27,">="&D3-$AA$1,$D$3:$X$27,"<="&D3+$AA$1)>1)

    where AA1 contains the factor you are looking for (e.g. 0.2)

    You can change the factor to observe different results.


  10. #10
    Quote Originally Posted by NBVC View Post
    Thanks NoS

    wowsers, please try selecting D3:X27 and apply conditional formatting using "use a formula to determine which cells to format", then insert formula:

    =AND(D3<>"",COUNTIFS($D$3:$X$27,">="&D3-$AA$1,$D$3:$X$27,"<="&D3+$AA$1)>1)

    where AA1 contains the factor you are looking for (e.g. 0.2)

    You can change the factor to observe different results.
    Thankyou NBVC, however this isnt working correctly....I substituted AA1 in the formula with the factor I was looking for and it ended up highlighting all the cells I had highlighted and then some I didnt even highlight. What seems to be the problem?

Page 1 of 2 1 2 LastLast

Posting Permissions

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