Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining sumproduct and average
If you are comparing the average of B3:B7 to the Average of B2:B6, then you
are really only comparing B7 to B2. =Sumproduct((B7:G7B2:G2)*1) should give you the count you want. if I have 1 2 3 4 5 6 in B2:B7, then the average for B3 to B7 is (6 + 5 + 4 + 3 + 2)/5 and for B2 to B6 is (5 + 4 + 3 + 2 + 1)/5 the (5 + 4 + 3 + 2)/5 is common to both, so the comparison resolves to is 6/5 1/5 or just is 6 1 Regards, Tom Ogilvy "Robert Pettit" wrote in message ... Sorry Im not explaining this at all well, Say I have spread sheet like this: Worksheet 1 A B C E F G H I 1 NAME1 NAME2 NAME3 ETC ETC ETC 2 1/1/03 1 1 1 1 1 1 3 2/1/03 1 1 1 1 1 1 4 3/1/03 1 1 1 1 1 1 5 4/1/03 1 1 1 1 1 1 6 5/1/03 1 1 1 1 1 1 (F) 7 ETC 1 1 1 1 1 1 (F) 8 ETC 1 1 1 1 1 1 (F) 9 ETC 1 1 1 1 1 1 (F) 10 ETC 1 1 1 1 1 1 (F) Worksheet 2 A B C E F G H I 1 NAME1 NAME2 NAME3 ETC ETC ETC 2 1/1/03 Below are averages of each column in 3 2/1/03 Worksheet 1 4 3/1/03 5 4/1/03 Ave Ave Ave Ave Ave Ave 6 5/1/03 Ave 1 1 1 1 1 (F2) 7 ETC 1 1 1 1 1 1 (F2) 8 ETC 1 1 1 1 1 1 (F2) 9 ETC 1 1 1 1 1 1 (F2) 10 ETC 1 1 1 1 1 1 (F2) Worksheet1 would be my original data and (F) = formula =Sumproduct((b6:h6b6:h6)*1). Telling me how many on the 5/1/03 are greater than 4/1/03. This is ok but what I actually want is averages. on Worksheet2 I use =average(b2:b6) of worksheet1 in b5, =average(c2:c6) in c5, I average e2:e6, f2:f6, g2:g6 and h2:h6 in d5, e5, f5, h5, g5 of the other worksheets. That gives me one row of data the I move down to b6 and average b3:b7 etc. Then I use sumproduct at the end of the row (F2). What I trying to do is avoid using another worksheet and enter a formula at (F) which will Average each column and then count how many are greater than the day before. I guess it wuld have to be a long formula because it needs to average say b2:b6 then b3:b7 compare second average with first then move to next column and repeat. I use alot more data than above spread over 5 worksheets so Id have to add another 5 worksheets to do it my way. Again thankyou for any help 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 average | Excel Programming |