Help analyzing my data.

**Shindel** · 06-03-2011, 01:04 PM

Hello,

I have a list of data that I would like to get the difference of and highlight it if it is out of range of what I would like.

For example:
A1 is 50
A2 is 55
The difference would be 5
Since it is above 1 that gets highlighted.
I would like that done for every cell in the column. A1, A2: A2, A3: A3, A4 ect.

I am not sure if this is the right section to post it in but any help on this would be greatly appreciated!

**Armitage2k** · 06-03-2011, 01:43 PM

Hi,

try conditional formatting. Just select the column the difference and via format - conditional formatting - choose the color of the pattern in case the cell value is greater/less than X. no need for a formula in my opinion.

Regards,
A2k

**MarvinP** · 06-03-2011, 01:53 PM

Hi Shindel and welcome to the forum,

Conditional Formatting may do what you want. See the attached and then look at the Conditional Formatting formula assigned to Column A.

Change some of the numbers in column A to see if it is working like you want. Then study Conditional Formatting.

**Shindel** · 06-03-2011, 03:55 PM

Thanks for the suggestions!

That did work to how you guys explained and based off of the information I gave but it does not help what I'm looking for.

I have attached part of the data for a better reference.

Here is exactly what I need out of this.

If I pick row 50 (A-Y of my headers)as a reference point I will need to find any other row that is within difference of 1 or less.

For example:
My reference is
A B C D
30.1 52.2 62.8 12.1 (My reference row)

I should be able to find out of any other row that is a difference of 1 or less respectively no matter what the reverence is.

A B C D
31.0 52.9 61.9 12.0 (Good data)
31.1 55.3 62.9 12.1 (Bad data because B was off)

I apologize if this seems confusing and appreciate any and all help regarding this. I lost contact with the friend I have that was able to do this for me.

**MarvinP** · 06-03-2011, 04:18 PM

Ok Shindel,

Find the attached that needed a CSE formula to solve your problem. I made up some random data and made the reference row an input number on row 1. See if this works for you.

**Shindel** · 06-03-2011, 04:37 PM

MarvinP,

Sorry I failed on adding the attachment. Here it is.

I like what you did there and it seems like it's going in the right direction. It is hard to tell because I don't know what the numbers in I represent and I have no clue how you did that.

I apologize for my noobness with excel.

**MarvinP** · 06-03-2011, 06:06 PM

Hi Shindel,
Find the attached with your data and my formulas.

So here is what I think you wanted.

You tell me a row or your data, like row 10 for example. Then Excel's job is to look at all the other rows and subtract the values in the same column and see if it is less than or equal to 1. You need to do this for all Columns D through AA. If the biggest difference between these numbers is less than (or equal to) 1 they you want to notice it.

I had did this with an Array or CSE or Ctrl + Shift + Enter formula. This is real fancy stuff.

What the formula does is subtracts each number for each cell in the ref row to each cell in the same column of all the other rows. It does an Absolute Value of the difference because I think you want that and then finds the Maximum of all these differences. See the formulas in column AC.

Then I look at all these maximum differences and Conditional Format the ones that are less than 1.

Problem solved for me but you need to study a while. Here are some links
http://www.cpearson.com/excel/ArrayFormulas.aspx (my favorite)
http://office.microsoft.com/en-us/ex...001087290.aspx

I hope this helps.