Find the first % value > greater the 1% for each day

[Jazntails]

Need help figuring out a formula to dynamically find the first % value > greater the 1% for each day.
And also the record number or line.
column A-D is the data set. Very large data set.
column H-J is on a different sheet.
I've used "match(" to find a record number but can't figure out how to get it to dynamically lookup the dates in column A.
I've used "index(" and "match(" to find the record of the first % value > greater the 1% for a day not dynamically.
Any help would be amazing.
View attachment 83076

 

[Jazntails]

pic below

 

[awoohaw]

What version of excel are you using?
Please put what version of excel you use in your profile as a solution in one version of excel will not work in other.

 

[fjns]

Hi, see the linked file for a possible solution...

The formulas used in the table:
I4: =IFERROR(MATCH(1,(A:A=H4)*(D:D>0.01),0)-MATCH(1,(A:A=H4)*1,0)+1,"")
J4: =IFERROR(INDEX(D:D,MATCH(1,(A:A=H4)*(D:D>0.01),0)),"")

test1.xlsx

 

[awoohaw]

You can try this as well. This give you the location to use in the index formula.

=1/MAX(IFERROR(1/((--(F3=$A$3:A27))*(--($D$3:D27>0.01))*(ROW($A$3:A27)-ROW($A$3)+1)),0))

Book1
	A	B	C	D	E	F	G	H
1
2	Date	Time	Bal	%
3	18-Feb-23	11:00	475.00	-5.00%		18-Feb-23	4
4	18-Feb-23	12:27	492.15	-1.57%		17-Feb-23
5	18-Feb-23	12:47	500.90	0.18%		16-Feb-23
6	18-Feb-23	13:02	510.65	2.13%		15-Feb-23
7	18-Feb-23	13:10	518.28	3.65%		14-Feb-23
Sheet2

Cell Formulas
Range	Formula
G3	G3	=1/MAX(IFERROR(1/((--(F3=$A$3:A27))*(--($D$3:D27>0.01))*(ROW($A$3:A27)-ROW($A$3)+1)),0))

 

[Jazntails]

Thanks.. works well.
How do I add to the formula if not a positive % day, Cell J4 want to return the last % for the day?
Cell I4 return the total records for the day?

 

[awoohaw]

what version of excel are you using? can you install a mini worksheet using the xl2bb add in (link below)?

 

[fjns]

Hi, the new formulas for I4 and J4:
=IFERROR(IF(SUM((A:A=H4)*(D:D>0.01))=0,IF(SUM((A:A=H4)*1)=0,"",SUM((A:A=H4)*1)),MATCH(1,(A:A=H4)*(D:D>0.01),0)-MATCH(1,(A:A=H4)*1,0)+1),"")
=IFERROR(IF(SUM((A:A=H4)*(D:D>0.01))=0,INDEX(D:D,MATCH(H4,A:A,0)+SUM((A:A=H4)*1)-1),INDEX(D:D,MATCH(1,(A:A=H4)*(D:D>0.01),0))),"")

test2.xlsx