combining sumproduct and average

[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

 

[Tom Ogilvy]

=Sumproduct((A2:F2>A1:F1)*1)

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

=AVERAGE((A2:F2>A1: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:F2>A1:F1)*1)/columns(A2:F2)

entered normally.

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

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


Regards,
Tom Ogilvy

 

[Robert Pettit]

Thanks for your reply. The formulas work fine but dont quite do what Im
looking for, my fault after reading my message its not very clear. I
want to average say A1:A10, B1:B10, C1:C10 etc all individually then
count the difference between yesterday and today. The formula I used
with inderect in, averaged these ok I couldnt sumproduct them without
first entering the formula as an array across say 20 cells, then using
sumproduct in the 21st cell. Basically if I have Dates from 1/1/03 to
todays date in column A, Then results for each day in columns B to any
number of colums say for now 10 cols J, I want to add in column l
against todays date, the number of aveaged data (average of data from
say 20 days ago to today) in row todays date B:J are greater than
yesterdays. With the inderect formula Ive been averageing each column
B:J entering the results for each column then counting how many greater.
My data is share prices, column A has dates and B1 to Z1 has share
names, Then b2:z2 downwards has prices. Hope you can advice
Regards Robert