Confused Criteria?: SumIfs() time values in cells versus those from excel TIME() func

[OaklandJim]

Trying to use multiple criteria in SumIfs on a series of 8760 hourly data including time-of-day (SeriesTimes) and values to sum (SeriesScaledValues). Works well, sort of.

For two criteria in Sumifs I use values in two cells whose values are 1. start time (rrStartTime1) and 2. end time (rrEndTime1). (FYI rr indicates relative row addressing.)
I looked, the serial time values are correct in those two cells.

If I use both CELL values for criteria it works. If I use two EXCEL TIME FUNCTION generated values it works. But, sometimes I also need to put in a hardwired start or end time. But, of course I cannot figger out how to do that.

Funcs 1 & 2 work, #3 does not.

=SUMIFS(SeriesScaledValues,SeriesTimes,">="&rrStartTime1,SeriesTimes,"<=" & rrEndTime1)

=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & TIME(1,0,0),SeriesTimes,"<"& TIME(5,0,0))

=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & rrStartTime1, SeriesTimes, "<"& TIME(1,0,0))

 

[joeu2004]

It would be useful if you provided an example Excel file that demonstrates the problem, or at least the formulas(?) that generate SeriesScaledValues and SeriesTimes. Upload the example Excel file to a file-sharing website, and post the download URL in a response. I like box.net/files. Caveat: Some participants will object because they cannot or will not download files.

I suspect that the problem is due to binary arithmetic anomalies in the SeriesScaledValues and/or SeriesTimes; probably the latter. SeriesTimes is not the same value as 1:0:0 when formatted to 15 significant digits, which is what SUMIFS compares.

The remedy: for any time calculation, explicitly round to the nearest second, to wit: =--TEXT(calculation, "[h]:m:s").

BTW, I suspect that the problem seems to be with only 1:0:0 only by coincidence.

 

[OaklandJim]

Thanks a lot Joe. I'll fiddle some more. Same problem with TIME(5,0,0). Kluge is to assign a time value using Time() func in name's RefersTo. If I use that name in formulas it works. Odd that. Seems inconsistent.

 

[joeu2004]

Thanks a lot Joe. I'll fiddle some more. Same problem with TIME(5,0,0). Kluge is to assign a time value using Time() func in name's RefersTo. If I use that name in formulas it works. Odd that. Seems inconsistent.

Funny! That's how I read your original posting originally. But then I saw that you wrote:

Funcs 1 & 2 work, #3 does not.
=SUMIFS(SeriesScaledValues,SeriesTimes,">="&rrStartTime1,SeriesTimes,"<=" & rrEndTime1)
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & TIME(1,0,0),SeriesTimes,"<"& TIME(5,0,0))
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & rrStartTime1, SeriesTimes, "<"& TIME(1,0,0))

No matter. There is nothing "odd" or "inconsistent"; simply "by coincidence", as I said.

You still do not provide concrete examples (values and formulas, or downloadable file), as I requested. So I will not be able to help you further.

But just to help you understand the "coincidence" of it all, consider the following example.

	A	B	C
2	11/24/2019 1:00
3	11/24/2019 6:00
4	5:00	0.208333333335759	A4: =A3-A2
5	5:00	0.208333333333333	A5: =TIME(5,0,0)
6	FALSE		A6: =A4 < A5
7
8
9	11/24/2019 2:00
10	11/24/2019 7:00
11	5:00	0.208333333328483	A11: =A10-A9
12	5:00	0.208333333333333	A12: =TIME(5,0,0)
13	TRUE		A13: =A11 < A12

(Sorry about the format of the table. The new forum interface is "broken", IMHO.)

Column B shows the adjacent values in column A formatted as Number with 15 decimal places, in order to display 15 significant digits for these examples.

The point is: when A2 and A3 contain date and time, the binary estimate of the time part has less precision; consequently, their difference has less than precision. Similarly with A9 and A10.

Sometimes the result of the calculation is greater than 5:00 (A6); sometimes it less than 5:00 (A13); and sometimes it might be equal.

Note that we cannot see the difference even when A4:A5 and A11:A12 are formatted as Custom [h]:mm:ss.000 .

Be that as it may, all of that is wild speculation, again because you neglect to provide concrete examples for me to work with.