Go Back   Hardware Canucks > SOFTWARE > O/S's, Drivers & General Software

    
Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old October 27, 2010, 12:06 PM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default Question for the Excel guru's out there

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!
Reply With Quote
  #2 (permalink)  
Old October 27, 2010, 12:28 PM
CMetaphor's Avatar
Quadfather
 
Join Date: May 2007
Location: Montreal, Canada
Posts: 5,002

My System Specs

Default

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...
__________________
"Backed by common sense and physics!" -Squeetard
Opteron Server for Sale! http://www.hardwarecanucks.com/forum...ade-ideas.html
Reply With Quote
  #3 (permalink)  
Old October 27, 2010, 12:29 PM
Eldonko's Avatar
Hardware Canucks Reviewer
 
Join Date: Apr 2007
Location: Calgary, AB
Posts: 5,445
Default

Use conditional formatting, takes 2 seconds and you dont need a formula.
__________________
Donkeys kill more people annually than plane crashes or shark attacks.
Reply With Quote
  #4 (permalink)  
Old October 27, 2010, 12:30 PM
CMetaphor's Avatar
Quadfather
 
Join Date: May 2007
Location: Montreal, Canada
Posts: 5,002

My System Specs

Default

Quote:
Originally Posted by Eldonko View Post
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.
__________________
"Backed by common sense and physics!" -Squeetard
Opteron Server for Sale! http://www.hardwarecanucks.com/forum...ade-ideas.html
Reply With Quote
  #5 (permalink)  
Old October 27, 2010, 12:32 PM
Eldonko's Avatar
Hardware Canucks Reviewer
 
Join Date: Apr 2007
Location: Calgary, AB
Posts: 5,445
Default

Conditional formatting is dynamic. Change a value and the highlighted cells change as well.
__________________
Donkeys kill more people annually than plane crashes or shark attacks.
Reply With Quote
  #6 (permalink)  
Old October 27, 2010, 12:51 PM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

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.

Last edited by GregH; October 27, 2010 at 12:56 PM.
Reply With Quote
  #7 (permalink)  
Old October 27, 2010, 01:52 PM
CMetaphor's Avatar
Quadfather
 
Join Date: May 2007
Location: Montreal, Canada
Posts: 5,002

My System Specs

Default

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?
__________________
"Backed by common sense and physics!" -Squeetard
Opteron Server for Sale! http://www.hardwarecanucks.com/forum...ade-ideas.html
Reply With Quote
  #8 (permalink)  
Old October 27, 2010, 02:00 PM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

Quote:
Originally Posted by CMetaphor View Post
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.
Reply With Quote
  #9 (permalink)  
Old October 27, 2010, 02:26 PM
CMetaphor's Avatar
Quadfather
 
Join Date: May 2007
Location: Montreal, Canada
Posts: 5,002

My System Specs

Default

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?
__________________
"Backed by common sense and physics!" -Squeetard
Opteron Server for Sale! http://www.hardwarecanucks.com/forum...ade-ideas.html
Reply With Quote
  #10 (permalink)  
Old October 27, 2010, 02:32 PM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

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.
Reply With Quote
Reply


Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help...Default pasting options in Excel 2007 Eagle Eye O/S's, Drivers & General Software 5 March 23, 2010 12:03 AM
EXCEL help LC0g O/S's, Drivers & General Software 15 February 3, 2010 11:55 AM
question for wc guru's gingerbee Water Cooling 13 March 31, 2009 08:52 PM
excel - timetable for work CTA O/S's, Drivers & General Software 0 March 26, 2009 02:04 PM
question for you rosetta guru's Phobia HardwareCanucks F@H Team 12 February 21, 2009 08:30 AM