=SUM (Question)

Mike1w · #1 06-26-2009, 10:55 AM

Attached is an example and I known there has to be an easier way than how I am doing this. Column “A” is a date/time stamp, and adjacent columns is numerical data associated with the time/date. The increments are 1min. intervals however I wanted (in column P) add totals from column B in thirty (30) min. increments and avg. that 30 min. period. For example cell P1 would add thirty minutes from column B (1-30) and then divide that total by 30 to get an average; P2 to be the next 30 minutes, etc. is there an easier way to do this than manually selecting the data? I was providing a sum(Bx:Bx)/30 to get my results but had to manually select every 30 min. Set, I was trying to find a way to simplify this any help is greatly appreciated. Thanks.

**daddylonglegs** · #2 06-26-2009, 11:58 AM

Try this formula in P1 copied down to P48 to average every 30 entries in column B

=AVERAGE(OFFSET(B$1:B$30,(ROWS(P$1:P1)-1)*30,0))

Mike1w · #3 06-26-2009, 01:05 PM

Thanks DaddyLongLegs it works but in trying to understand what the formula is doing correct me if I am wrong and please fill in the blanks, Here Go’s:
The average formula is averaging cells B1-B30 the offset command is what I do not understand it is offsetting (reference cells (B1-B30) adjusted 30 cells beyond the last cell utilized in the formula above (P)? Just trying to understand the usage of the offset command. Also is there anywhere where I can find a comprehensive list of excel functions and their definitions? Thanks again.

Mike1w · #4 06-29-2009, 08:18 AM

Thanks for your help on this, but I attempted to modify this for another sheet this time with combining 5-minnute increments into blocks of 30min increments however the last couple are not coming up correctly? What am I doing wrong? Thanks again. attached is a test spreadsheet.

martindwilson · #5 06-29-2009, 08:35 AM

try
=SUM(OFFSET(B$1:B$7,(,0)) in row 43 it returns 0
average(0) returns #div0!
then look at the offset
=(ROWS(U$1:U43)-1)*7 = 294
now look at rows 293 onwards there is no data

Mike1w · #6 06-29-2009, 05:13 PM

Yeah, thats what I cannot figure out I modified the original to fit the cirtera on this sheet (or so I thought) but it does not average correctly I tried to find some infomration on the offset forumla / condition but was hoping someone hear could better explain it to me. Offset(reference,rows,cols,[height],[width], the reference is the original cells utilized (correct?) is this case B1:B7 – rows, and cols, I am not sure?

mubashir aziz · #7 06-30-2009, 12:30 AM

Your Formula is
U1=AVERAGE(OFFSET(B$1:B$7,(ROWS(U$1:U1))*7,0))

you are missing
U1=AVERAGE(OFFSET(B$1:B$7,(ROWS(U$1:U1)-1)*7,0))

But i'm afraid in this case first time it will calculate the average from B1:B7 (12:00 ~ 12:30 ) and next time it will give the average B8:B14 (12:35 ~ 01:05 ) which i think not your requirement .......
I think you should start from 12:05 to 12:30; 12:35 to 01:00 ; 01:05 to 01:30 ; so your problem can be sort out ... like in statistics we mention C.B. for this use a C.L. But in current situation you have to decide what you exactly required ....... then the formula can be altered ........

DonkeyOte · #8 06-30-2009, 03:01 AM

Probably also worth making the point that not all your time intervals are even in Column A meaning you need a variable sized range (see A232:A233 for ex.)

Mike1w · #9 06-30-2009, 01:12 PM

All of the time increments are 5min apart in this scerino I am trying to average over 30min incriments (Ex. 12-12:30; 12:30-1:00; 1:00-1:30; 1:30-2:00…etc) correct me if I am wrong but =AVERAGE(OFFSET(B$1:B$7,(ROWS(U$1:U1))*7,0)) would apply to the first cell in U1 and U2 would be =AVERAGE(OFFSET(B$1:B$7,(ROWS(U$1:U2)-1)*7,0)) the -1 then including the 12:30 from the 12:30-1:00 average? But for some reason when I get to the bottom something happens and the last couple to not calculate correctly getting the #div/0! Error and I cannot figure out why if all the others are ok?

martindwilson · #10 06-30-2009, 03:00 PM

as i said before when your offset range is past the last set of data in column then you are trying to average nothing
say you had this
=AVERAGE(OFFSET(A1:A2,2,,2))
this would average cells 2 down and 2 deep i.e a3:a4
if no value in a3:a4 you get #div0! (however if you had 0 in a3 or a4 you'd get 0 )

ok heres an example of summing and averaging every 2 rows and 7 rows perhaps it will give you a clearer picture.
use of rows() returns row number useful for incrementing numbers so row() in row one =1 in row 2 =2
thats ok if you want to start from row one and drag down but if you started in row 5 and wanted it to =1 you'd have to deduct the rest so row()-4 would =1. to avoid this we use rows() [or columns()if going horizontaly]
rows() counts the number of rows in a range rows(a1:a1) =1 by fixing the first reference (making it absolute) we use rows($a$1:a1) this returns 1 but on fill down the second part increments while the first stays fixed
so rows($a$1:a1) =1 then rows($a$1:a2)=2 rows($a$1:a3)=3 and so on).(you can use what you like rows($zz$1:zz2)still =2)
in the examples we want to always offset from a1:a2 or g1:g7

so the first part of the formula used absolute ref to fix tham $a$1:$a$2 / $g$1:$g$7
now we use that rows() value as the offset in the row arguement

in the first instance we want to return first a1:a2 then a3:a4 then a5:a6.......
so for a1:a2 offset is 0 then offset to a3:a4 is 2 (difference from a1 to a3)
to achieve the offset with row() or rows we deduct 1 then multiply by the required offset
so in row 1 (rows()-1)*2 = (1-1)*2 = 0 so offset is 0
in row 2 (rows()-1)*2 = (2-1)*2 = 2 so offset is 2
in row 2 (rows()-1)*2 = (3-1)*2 = 4 so offset is 4
HTH

Welcome to the Excel Forum

Forum Statistics