Average dynamic data via month???

[f4stunter]

I've been reading post after post and I'm just not getting it or it doesnt apply to what I have. Here's the layout. Excel 2007. I have a .CSV I generate from another program that has the date in column A&B(may not need both columns),time incremented every 15 mins in Column C, Flow data from 2 diff flow meters in D & E. Now in another worksheet I'm averaging all flow data for each meter for each day. The problem lies in the program that creates the CSV will only create by number of days, so the CSV will always have a few hours of run over time into the next month. This formula =AVERAGEIF(A:A,A9,E:E) will work for day 1 and =AVERAGEIF(A:A,A9+1,E:E) for day 2 and so on, however I need to make sure it doesnt rollover into the next month. If you have anymore questions or would like to see the actual data im working with just ask. Thanks, Jon.

 

[Kaps_mr2]

pls post the data. Thks

Kaps

 

[f4stunter]

Not sure how to post the actual data so I'll post screenshots and if you need the data file i can send it. The screenshots are the fist part of the data, the transition from day 1 to day 2 and the end of data where it transitions to the next month.

 

[f4stunter]

P.S. I can modify the CSV to some extent. I can add or subtract columns with more or less date fields and what not. If this were in a sql database I'd already have it finished but I just dont know how to query the data.

 

[f4stunter]

I figured it probably would help if I also posted the actual report that im trying to configure the data for. As you can see the formula I posted would work fine up till the end of the month where I would start getting the next months calculations in the wrong fields. Also on months that dont have 31 days and especially february I would have a problem. I dont mind if the box is left blank or has the div error but I can't have false data calculated.

 

[f4stunter]

I guess sometimes you just gotta figure it out yourself, lol. How's this look to you guys? =IF(MONTH(Data!A9)=MONTH(Data!A9+30),AVERAGEIF(Data!A:A,Data!A9+30,Data!E:E),"") If the month rolls over into the next month then it sets the box to empty so it doesnt get calculated.