Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averages
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averages | Excel Discussion (Misc queries) | |||
Averages | Excel Discussion (Misc queries) | |||
Averages | Excel Discussion (Misc queries) | |||
30, 60 or 90 day averages | Excel Worksheet Functions | |||
Averages | Excel Worksheet Functions |