PDA

View Full Version : [SOLVED] Average times as long as between 2 dates



CuriousGeorg
09-24-2013, 12:26 AM
Morning,

a raw data file with lots of data input on different dates. On Sheet Combined column Q I have times ([h]:mm format).

What I need on my summary page is to be able to average the times based in Column P as long as they fall within the dates specified in cells (Summary G3 and H3).


I have tried

=AVERAGEIFS(Combined!Q:Q,Combined!G:G,"<="&H3,Combined!G:G,">"&G3)

However I get the answer as ##################

:think:


any help is great. (be even nicer if there was a vba code for it!)

CuriousGeorg
09-24-2013, 01:05 AM
oh and there's another condition.. Column P MUST say "Finance"

SamT
09-24-2013, 05:49 PM
Have you tried formatting the formula column as a number with fewer decimal places?

Widen the column until you see the ##'s go away.

CuriousGeorg
09-25-2013, 12:03 AM
it appears that somehow, (when converted into number) that the result is a negative which is somewhat a mystery.

Before I do a full data validation check (as there are 2k records) do I assume that this IS the correct way to do it?

CuriousGeorg
09-25-2013, 01:21 AM
ok it seems that was right.. there were a LOT of errors,

now... seeing how that "works".. how do I add a condition in there? as In the average as above.. but IF range N:N = finance say?


Should this work?

=SUMPRODUCT((Combined!$N$2:$N$10000="Finance")*(Combined!$G$2:$G$10000>=$E$3)*(Combined!$G$2:$G$10000<$F$3)*(Combined!$P$2:$P$10000))

If the date ranges are e3 and f3 Combined!G:G being the dates to look for. Combined!P:P being the times i need to "sum"

SamT
09-25-2013, 08:23 AM
:dunno:
Try =SUMIF(allDatesColumn,And(>DateCell,<DateCell),TimeColumn)/CountIf(allDatesColumn,And(>DateCell,<DateCell))

That Formula must be Entered by pressing Ctrl+Shift+Enter (Its a CSE or Array Formula)

The negative answer is because some of the Multiplicands are Booleans that result in a False.