Average Formatting

I have data over a range of three days. The date and time at minute intervals is in one column, and the data at each of those intervals is in another. How can I create a forumla that will say something like: "if the time is 7:00:00 am on any of these three days, sum the value of the corresponding data". And allow this to occur every minute, so there is a similar one for "if the time is 7:01:00..." There are 3 data points for each minute interval - one representing that time at each day.

Anyone know how to do this?

seanyc,

Can you please close off your open threads.. as you are leaving us hanging on those threads. If you don't close them off and respect the people's time and effort to help you, you will find less people willing to help in the future.

Also, If I remember correctly you are using non contiguous ranges.. please specify and also post a sample workbook so people can more accurately help.

In D4:

=SUMPRODUCT(--(MOD($A$4:$A$1848,1)=MOD(A4,1)),$B$4:$B$1848)

copied down

You asked for the sum... so it's giving the sum of all the cells in column B that have a corresponding time in column A of 7:00 AM.

If you want average:

=SUMPRODUCT(--(MOD($A$4:$A$1848,1)=MOD(A4,1)),$B$4:$B$1848)/SUMPRODUCT(--(MOD($A$4:$A$1848,1)=MOD(A4,1)))

I see 7:00 AM entries in cells:

B4: 20
B731: 6
B1347: 1

which adds up to 27... so average is 27/3=9

Try:

Please Login or Register  to view this content.

btw: this does not have to do with Conditional formatting as per your sample.. can you update your title appropriate to the actual problem.?

Edit your first post... click Go Advanced.. fix the title.

That is what I had assumed you had at the beginning...

Also, If I remember correctly you are using non contiguous ranges.. please specify and also post a sample workbook so people can more accurately help.

why are we going back to this now!

Did we not work on that the other day? I mean getting average over the 3 days on a minute-by-minute basis, with this setup? What is the difference here?

If you enter the first time in O2 (e.g 7:00 AM), then try:

=SUMPRODUCT(--(ROUND(MOD($A$4:$K$1848,1),6)=ROUND(O4,6)),$B$4:$L$1848)/SUMPRODUCT(--(ROUND(MOD($A$4:$K$1848,1),6)=ROUND(O4,6)))

I added the ROUND() function to ensure matches as when times are manually entered and you are comparing to date/time entries, Excel will not match them accurately because of precision issues... so the Rounding makes sure to shorten the serial time to more compatible values...

The formula needs to be outside of the A4:M1848 range... put it in P4 copied down.

Note: It is probably easier to put 7:00 in O2, 7:01 in O3, then select both and fill down to automatically add 1 minute intervals.

Please see attached.

I also added an error-handler to return a 0 instead of #DIV/0! error when a time slot doesn't exist in the database...

Please Login or Register  to view this content.

Do you mean in P2?

Please Login or Register  to view this content.

copied down..

Great, please don't forget to mark the thread as Solved... and close off any lingering threads you have open.

Don't know what you mean.. you want to change O2 to =NOW() and O3 to =O2+TIME(0,1,0) copied down?

tRY:

Please Login or Register  to view this content.

Where C4 is cell containing formula =NOW() on another sheet...

That is like a vlookup! Implement it properly!