Q1. Is there a way to average a range(dnamic) of 6 or 7 numbers with conditions?
Q2 Failing number 1.
I have a list of calculated figures, this list has a large spread and a large scatter. From this i need to filter and trend the points.
The filter should look at a number (call it the primary) and take into account the previous six days (or so) numbers (call these the rolling average) and judge wether the primary number should be there.
simple so far, well relativly, the tricky bit is, once you have assessed the primary and decided that it is not valid, how do you omit it from the rolling average?
The code I have used is in three colums (M,N,O), on with a list of numbers:
Delta Performance
-3.7
-6.5
-10.1
-7.2
-28.6
-4.2
-13.8
-13.1
-15.4
-1.0
-3.9
-8.1
-21.4
-17.2
-18.8
-18.9
-17.2
-10.3
-14.8
-15.6
-21.6
-16.0
-19.6
-13.3
15.7
-18.1
-8.4
-10.8
The next one has this formula, starting from row 8:
=IF(ISERROR(M13),"",IF(IF(AND(M13<(1-Variables!$H$9/100)*O12,M13>(1+Variables!$H$9/100)*O12),M13,FALSE)=FALSE,"*",M13))
where variables!H9 is say 30
and a third column with this code:
=AVERAGE(IF(N13="*",O12,N13),IF(N12="*",O11,N12),IF(N11="*",O10,N11),IF(N10="*",O9,N10),IF(N9="*",O8,N9),IF(N8="*",O7,N8),IF(N7="*",O6,N7),IF(N6="*",O5,N6),IF(N5="*",P4,N5))
starting also in row 8.
as I am sure you can see this is a very cumbersom and difficult code to use. I am trying to make it portable and wish to write it as a function but now I am getting a little over my head, so I thought i would tap the brains on this board to see if you could come up with a simplified version that could be made into a (quick) function as it is not only cumbersome but very, very slow (20000 rows or so)
Hope you can help and any more info just ask!!
Ed
This message was edited by Jackeb on 2002-04-29 05:48
This message was edited by Jackeb on 2002-04-29 05:48
Q2 Failing number 1.
I have a list of calculated figures, this list has a large spread and a large scatter. From this i need to filter and trend the points.
The filter should look at a number (call it the primary) and take into account the previous six days (or so) numbers (call these the rolling average) and judge wether the primary number should be there.
simple so far, well relativly, the tricky bit is, once you have assessed the primary and decided that it is not valid, how do you omit it from the rolling average?
The code I have used is in three colums (M,N,O), on with a list of numbers:
Delta Performance
-3.7
-6.5
-10.1
-7.2
-28.6
-4.2
-13.8
-13.1
-15.4
-1.0
-3.9
-8.1
-21.4
-17.2
-18.8
-18.9
-17.2
-10.3
-14.8
-15.6
-21.6
-16.0
-19.6
-13.3
15.7
-18.1
-8.4
-10.8
The next one has this formula, starting from row 8:
=IF(ISERROR(M13),"",IF(IF(AND(M13<(1-Variables!$H$9/100)*O12,M13>(1+Variables!$H$9/100)*O12),M13,FALSE)=FALSE,"*",M13))
where variables!H9 is say 30
and a third column with this code:
=AVERAGE(IF(N13="*",O12,N13),IF(N12="*",O11,N12),IF(N11="*",O10,N11),IF(N10="*",O9,N10),IF(N9="*",O8,N9),IF(N8="*",O7,N8),IF(N7="*",O6,N7),IF(N6="*",O5,N6),IF(N5="*",P4,N5))
starting also in row 8.
as I am sure you can see this is a very cumbersom and difficult code to use. I am trying to make it portable and wish to write it as a function but now I am getting a little over my head, so I thought i would tap the brains on this board to see if you could come up with a simplified version that could be made into a (quick) function as it is not only cumbersome but very, very slow (20000 rows or so)
Hope you can help and any more info just ask!!
Ed
This message was edited by Jackeb on 2002-04-29 05:48
This message was edited by Jackeb on 2002-04-29 05:48