Question for the Excel guru's out there

[GregH]

I've been too busy to dig into this, but hopefully someone with nothing better to do can help me out

I have a spreadsheet with a column of values.
I want to know if there's a formula that will examine those numbers and look for values that are significantly different from the other values in the column (say 30-40% different)
Even better if it can then highlight the cells where those variations are noted or do something to make them stand out.

I imagine it can be done via a macro too, but I don't have much experience using those.
Office 2007 or 2010 if that makes a difference.

I know I can manually add in a column to calculate the differences between values, but I don't want to clutter up my worksheet with unnecessary columns.

Thanks in advance!

[CMetaphor]

Hmmmm,

What if you take the average of all the values in the whole column then use that as a base reference to compare each individual value to? If you can do that, than a simple calculation (one per box) to compare that number to within 30-40% of the reference would work, no?

Ex:

1
1
1
3
1
1

averages out to: 1.3333, which ill call REF for now.
Then for each value box you use a formula like: IF((REF/A1) < 1.3) then (BOLD A1 *just an example*)

Copy that kind of formula to each data box and then you'd have:
1
1
1
3 *Bolded*
1
1
Which would make any value thats greater than 30% of the average turn bold.

My math might be off, but you get the idea...

[Eldonko]

Use conditional formatting, takes 2 seconds and you dont need a formula.

[CMetaphor]

Originally Posted by Eldonko

Use conditional formatting, takes 2 seconds and you dont need a formula.

That would only work with a set amount of data wouldnt it? The data values listed may change (he didn't specify) so a fixed conditional format wouldnt work if changes are made. I might be wrong though, im going off the top of my head.

[Eldonko]

Conditional formatting is dynamic. Change a value and the highlighted cells change as well.

[GregH]

Thanks for the hints guys - I just tried out conditional formatting on a test spreadsheet and it worked ok (set it to highlight cells over or under 2 std. dev of the average)

I'll try the same on the actual spreadsheet and see what happens.

*EDIT*

OK, it's working, but is there any way I can apply that conditional formatting to the entire worksheet rather than doing it a column at a time? I have around a hundred columns to do, so that'd certainly save time.

If I highlight all the columns it then averages all of them and applies the conditional formatting to that value rather than the average for just that column.

[CMetaphor]

If you want the same formula copied to different cells you can drag the bottom right little black square from a cell that already has a formula down or right as much as you wish (normally works, but check to make sure it copied the formula but incremented cell names where necessary).

I don't understand that last part of what you said... it is working, but it isnt?

[GregH]

Originally Posted by CMetaphor

If you want the same formula copied to different cells you can drag the bottom right little black square from a cell that already has a formula down or right as much as you wish (normally works, but check to make sure it copied the formula but incremented cell names where necessary).

I don't understand that last part of what you said... it is working, but it isnt?

Problem with that is that this isn't a formula such as a SUM that you can copy over.

Say column 1 is all ones, with an outlier of 3.
Column 2 is all fives, with an outlier of 10.

If I select the entire range (column 1 and 2) to apply the formatting to, it takes the average of BOTH columns, and identifies outliers based on the average of both columns.
I want it to look at each column and it's outliers individually, as each column is independent of the one next to it.

Does that make sense?

I'm just trying to avoid having to conditionally format each column individually, as that will take a long time since there are so many columns.

[CMetaphor]

That's odd... I don't know if that's the conditional formatting or not, but i know dragging the formula using my method works (at least it does in OpenOffice, i just tested it). Like if you drag a whole column's formula which has a formula that uses "B1:B6" in it, those values should translate to "C1:C6" if dragged one column over. Is it not doing this?

[GregH]

No, because conditional formatting is not a formula in the sense that your B1:B6 or whatever one is.

With your one you have a cell with a formula in it. When you click that cell it'll outline the cells included in the formula.

With conditional formatting none of the cells contain formulas, so there are no outlined cells where you can drag the formula over.