=Sumproduct((A2:F2A1:F1)*1)

Should adjust the formula automatically when you copy it down since you are
using relative references.

=AVERAGE((A2:F2A1:F1)*1)
Entered with Ctrl+Shift+Enter rather than just enter will give you the
percentage of the values greater than yesterday.

or
=Sumproduct((A2:F2A1:F1)*1)/columns(A2:F2)

entered normally.

If you want the average of the values that are greater than yesterdays
values

=AVERAGE(IF(A2:F2A1:F1,A2:F2))
Entered with Ctrl + Shift+Enter


Regards,
Tom Ogilvy


"RobcPettit" wrote in message
...
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:F2A1: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