Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining sumproduct and average
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining sumproduct and average
=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining sumproduct and average
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining SUMPRODUCT with IF - help! | Excel Worksheet Functions | |||
If statement combining average | Excel Discussion (Misc queries) | |||
Combining Average, Offset & Vlookup | Excel Worksheet Functions | |||
combining weekday formula, average and going back 7 cells | Excel Discussion (Misc queries) | |||
Combining SUMPRODUCT and RANK functions | Excel Worksheet Functions |