Sequence of values (numer, dates, times etc) with multiple criteria.

[HighAndWilder]

Given that I have a sequence of values, times in this case, I'd like to produce what is in column F and G as a sequence from F2 to F16 and G2 to G16.

I don't want to have to work out where the formula needs to be placed (F4 and G4).

In this scenario there are two criteria (>=01:03:00 and <=01:16:00 ) but I want to be able to enhance such a formula to include multiple criteria.

The 'Value Here' value could be the result of another formula or hard coded.

How could I do this?

Thanks.

REGEXEXTRACT.xlsm
	A	B	C	D	E	F	G	H
1
2		01:03:00	01:16:00		01:00:00
3					01:02:00
4					01:04:00	01:04:00	Value Here
5					01:06:00	01:06:00	Value Here
6					01:08:00	01:08:00	Value Here
7					01:10:00	01:10:00	Value Here
8					01:12:00	01:12:00	Value Here
9					01:14:00	01:14:00	Value Here
10					01:16:00	01:16:00	Value Here
11					01:18:00
12					01:20:00
13					01:22:00
14					01:24:00
15					01:26:00
16					01:28:00
17
18
Sheet6

Cell Formulas
Range	Formula
E2:E16	E2	=SEQUENCE(15,1,TIME(1,0,0),1/720)
Dynamic array formulas.

 

[hagia_sofia]

Hello, would something like this be on the right track:

=LET(
s,E2:E16,
c,(s>=B2)*(s<=C2),
v,"Value Here",
IF(c,HSTACK(s,IF(SEQUENCE(ROWS(s)),TOROW(v))),""))

 

[barry houdini]

I'm not clear what values you actually want in column G, can you explain?

Note that using SEQUENCE like that for a sequence of time values may result in floating point errors which could affect any calculations based on those values, better to use a dragged down formula in E2 like this:

=TIME(1,2*(ROWS(E$2:E2)-1),0)

 

[Fluff]

As Barry has pointed out, the formula in col E could cause problems. Another option is

Fluff.xlsm
	D	E			H
1
2	01:00:00	01:00:00			TRUE
3	01:02:00	01:02:00			TRUE
4	01:04:00	01:04:00			TRUE
5	01:06:00	01:06:00			TRUE
6	01:08:00	01:08:00			TRUE
7	01:10:00	01:10:00			TRUE
8	01:12:00	01:12:00			TRUE
9	01:14:00	01:14:00			TRUE
10	01:16:00	01:16:00			TRUE
11	01:18:00	01:18:00			TRUE
12	01:20:00	01:20:00			TRUE
13	01:22:00	01:22:00			FALSE
14	01:24:00	01:24:00			FALSE
15	01:26:00	01:26:00			TRUE
16	01:28:00	01:28:00			TRUE
Data

Cell Formulas
Range	Formula
D2:D16	D2	=TIME(1,SEQUENCE(15,,0,2),0)
E2:E16	E2	=SEQUENCE(15,1,TIME(1,0,0),1/720)
H2:H16	H2	=D2=E2
Dynamic array formulas.

As can be seen in col H the times don't all match

 

[barry houdini]

Hey Fluff!

I like that method, I'm stealing it.....!

 

[HighAndWilder]

Hello, would something like this be on the right track:

=LET(
s,E2:E16,
c,(s>=B2)*(s<=C2),
v,"Value Here",
IF(c,HSTACK(s,IF(SEQUENCE(ROWS(s)),TOROW(v))),""))

Yes it would hagia_sofia.

It shows me how to apply multiple criteria which is what I was missing.

Thanks

 

[HighAndWilder]

As Barry has pointed out, the formula in col E could cause problems. Another option is

Fluff.xlsm
	D	E			H
1
2	01:00:00	01:00:00			TRUE
3	01:02:00	01:02:00			TRUE
4	01:04:00	01:04:00			TRUE
5	01:06:00	01:06:00			TRUE
6	01:08:00	01:08:00			TRUE
7	01:10:00	01:10:00			TRUE
8	01:12:00	01:12:00			TRUE
9	01:14:00	01:14:00			TRUE
10	01:16:00	01:16:00			TRUE
11	01:18:00	01:18:00			TRUE
12	01:20:00	01:20:00			TRUE
13	01:22:00	01:22:00			FALSE
14	01:24:00	01:24:00			FALSE
15	01:26:00	01:26:00			TRUE
16	01:28:00	01:28:00			TRUE
Data

Cell Formulas
Range	Formula
D2:D16	D2	=TIME(1,SEQUENCE(15,,0,2),0)
E2:E16	E2	=SEQUENCE(15,1,TIME(1,0,0),1/720)
H2:H16	H2	=D2=E2
Dynamic array formulas.

As can be seen in col H the times don't all match

Thanks Barry And Fluff.

I was only using times as an example but it was good that I did.

I will use the column D method in future.

Thanks.