help with formula

**Martindelica** · 10-09-2006, 02:26 AM

Hi there, Thanks for helping. I have a set of data below that I would like excel to locate and highlight the numbers that are within a band, say of 0.30. Can this be done?
A
22.1895
22.3079
22.4237
22.537
22.6476
22.7557
22.8611
22.9638
23.0638
23.1611
23.2556
23.3474
23.4364
23.5226
23.606
23.6866
23.7643
23.839
23.9109
23.9797
24.0456
24.1085
24.1684
24.2252
24.2789
24.3295
24.3771
24.4214
24.4627

I suppose somehow excel would have to find the same whole numbers, average them and apply the average as the middle number and find numbers that are within 0.15 on each side of the middle number. Is this possible?

**oldchippy** · 11-01-2006, 11:21 AM

Hi Martindelica,

Not sure whether I understand the question (or anybody else), but if you put this in B1

= -A1+A2

they are all within .30 or .15 of each other, can you give some more information

oldchippy

**Alfaholic** · 11-14-2006, 07:02 AM

Also not sure if I understand the question exactly, but I am pretty sure that Conditional Formatting can do the job you want.

If you select the cells with the values in, then select "Formats / Conditional Formatting" from the menu bar, you can put formulas into the conditions windows. So, if you want to find all values that are within, say 0.3 of the mean, your conditions may look something like this, where the ranges are the cells with the values that you want checked:

"Cell Value Is Between =AVERAGE($A$1:$A$8)-0.15 and =AVERAGE($A$1:$A$8)+0.15".

Then press the Format button, select, say, the Patterns tab, select a colour, and all cells in the highlighted range with a value that is within 0.3 of the average will be filled in the selected colour.