Highlighting extreme numbers in a range

[graemeal]

I have 10 numbers across a row from C to L and 2000 rows deep. The numbers are sourced from a live outside source daily, copied and pasted into the spread sheet each day. As in first day column C, next day D and so one until I get 10 or more columns of numbers.

After 10 or more days I am trying to get an average figure of those 10. The problem is sometimes the number can be zero for say 3 days in a row or one huge figure which upsets the average. Is there some way to highlight the cells that are extremely out of whack from the rest so I can adjust them. I need a rough average that ignores extremes

Hard to explain but say the numbers are as follows across the 10 columns

12,000 8,000 15,000 0 0 0 600,000 7000 16000 11,000

The rough average would be about 10,000 or 11,000 if it was not for the zero in 3 columns and the 600,000 in another. So is there some way to highlight with maybe some formula and maybe CF to tell me what cells are way off the mark?

I am guessing maybe to have an average in column M then have CF formula running down each column highlighting cells that are more or less than say 4 times the average figure.

Is there a better way? If not what would the CF formula asking IF column C is greater or less than 4 times colomn M

Thanks

Win 7 XL 2007

 

[VoG]

I would use Standard Deviations e.g.

=if(abs(m1-i1)>3*stdev(m1:m10),"Error","")

 

[graemeal]

Thank you kindly. Seems to work well although it does not highlight when there is a zero in a cell.

I was also after each indivual cell to be highlighted as i am looking at checking 20,000 cells

Just wondering what the formulas would be if I ran CF formula's down columns from C to L

I tried the below but obviously the "red" second one is incorrect as it highlights most?

Trying to hightlight each cell if the figure is 4 times greater or 4 times less than the average figure in M


=C2>(M2*3) GREEN

=C2<(M2*3) RED

Thanks

 

[VoG]

Maybe like this

=OR(C2="",C2 < M2*3)<m2*3)></m2*3)>

 

[graemeal]

Great. Thank you kindly

 

[graemeal]

not sure if i should reply to you or start a new post?

I am still trying to understand Standard Deviations. it seems that it is just what I am looking for. messing around with that formula you gave seems I may have explained ir badly =if(abs(m1-i1)>3*stdev(m1:m10),"Error","")

so hopfully more clear this time:

Say I have a row of 30 numbers A:J with an average figure of that range in cell K, I just to know if any numbers between A:J are say 5 times more than the average figure in K

And preferably if any are less than 5 times the average figure as well.

Thanks If I should start a new post just let me know

Regards

 

[graemeal]

Sorry meant from A:AD with the average in AE

 

[VoG]

Here is an example which may help. I used 1 x SD for illustration but it is more usual
to use 2 , 3 or 2.34 x SD to achieve a similar result.

Excel Workbook
	A	B	C
1	6	3.272783
2	8	5.4
3	1		Flyer
4	1		Flyer
5	4
6	4
7	10		Flyer
8	8
9	3
10	9		Flyer
Sheet12

 

[graemeal]

Thank you kindly. I will try and get my head around it over the weekend