Formula needed

wowzers

New member
Joined
Aug 12, 2013
Messages
7
Reaction score
0
Points
0
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 :frusty:


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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
need to remove /showdownload.php from middle of OP's link to get file.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

wowzers

New member
Joined
Aug 12, 2013
Messages
7
Reaction score
0
Points
0
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?
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
wowzers
when I tried this, it seemed to work OK with the suggested formula, but I did notice that with a tolerance of 0.2 most of the numbers had other ones within the tolerance, so that the CF would apply. I think there were maybe 3 with no others within the tolerance. I can't confirm this because I didn't save a copy and I can't get another copy of the file from filedropper.

I suggest you check the formatting formula for errors (it sounds like a range might be wrong). Also, check some of the addresses that are outside your conditional group against the conditional formula(e) to see if you can see why they are being formatted (multiple rules maybe?).

HTH

HTH
 
Top