R
RobcPettit
Hi, can anybody advice how to combine Sumproduct and Average. Ill try to
explain what I want to do and hope it makes sence.
A B C D E F G
1 1 2 3 4 5 6
2 1 2 3 4 5 6 =Sumproduct((A2:F2>A1:F1)*1)
3 1 2 3 4 5 6
4 1 2 3 4 5 6
4 1 2 3 4 5 6
5 1 2 3 4 5 6
At A10 I have an array, in this example it would fill A10 to F10 with
=Average(Inderect(Address(1,Column())&":"&Address(3,Column()))). This gives me
a 3 point average of A1:A3, B1:B3 etc. Ive found an array is the quickest way
to enter the average. In reality my date is 200 columns by approx 2000 rows
which will increase each day. What Im doing at the momment is two fold. Firstly
I need to know each day how many data are greater than yesterdays whence the
sumproduct in g2 which I copy all the way down to last row entered. This works
perfect. This is on the original data, what I then do is on another worksheet
is average the data, this can vary from a 3 point average to a 201 point
average, when this is on the worksheet I then use sumproduct to see again how
many are today are greater than yesterday. What Id like to know is can I
combine the sumproduct in g2 of the original data with the average. Im not
interested in the average value at all purly the difference between today and
yesterday. The only reason I use another worksheet is at the momment its the
only I can get around the problem. I hope this all makes sense. Also although
the Array Im using works well when I copy down it uses them same references so
I have to edit, is there a way around this.
To summarise, Im adding data to a worksheet, counting how many are greater
today than yesterday using sumproduct, I then want to average the original data
again count how many are greater than yesterday, all in one cell, without
havein to calculate the averages on another worksheet then use sumproduct.
Thankyou for any help
Regards Robert
explain what I want to do and hope it makes sence.
A B C D E F G
1 1 2 3 4 5 6
2 1 2 3 4 5 6 =Sumproduct((A2:F2>A1:F1)*1)
3 1 2 3 4 5 6
4 1 2 3 4 5 6
4 1 2 3 4 5 6
5 1 2 3 4 5 6
At A10 I have an array, in this example it would fill A10 to F10 with
=Average(Inderect(Address(1,Column())&":"&Address(3,Column()))). This gives me
a 3 point average of A1:A3, B1:B3 etc. Ive found an array is the quickest way
to enter the average. In reality my date is 200 columns by approx 2000 rows
which will increase each day. What Im doing at the momment is two fold. Firstly
I need to know each day how many data are greater than yesterdays whence the
sumproduct in g2 which I copy all the way down to last row entered. This works
perfect. This is on the original data, what I then do is on another worksheet
is average the data, this can vary from a 3 point average to a 201 point
average, when this is on the worksheet I then use sumproduct to see again how
many are today are greater than yesterday. What Id like to know is can I
combine the sumproduct in g2 of the original data with the average. Im not
interested in the average value at all purly the difference between today and
yesterday. The only reason I use another worksheet is at the momment its the
only I can get around the problem. I hope this all makes sense. Also although
the Array Im using works well when I copy down it uses them same references so
I have to edit, is there a way around this.
To summarise, Im adding data to a worksheet, counting how many are greater
today than yesterday using sumproduct, I then want to average the original data
again count how many are greater than yesterday, all in one cell, without
havein to calculate the averages on another worksheet then use sumproduct.
Thankyou for any help
Regards Robert