Spilled formula to return the min of a reducing range

[ED38]

Hello All,

I am blocked with a formula trying to get the minimum of a list of values below each row of the considered value.
Sorry, I am maybe not very clear it is not easy to phrase with words so I attached my case below, I am sure you will better understand.

Thank you for your help.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			14
3		Ranges		%				Discounted
4		lower	upper	discount			For each range :	Min	Max	Expected	to get this			My try But it removed the min of the whole H6:H18 range (6,6), not the min of the cells below each row!!
5		1	11	0,0%		13	1	1	11	4,40	56,0%			4,4
6		12	26	45,0%		12	2	6,6	14,3	3,23	58,1%			7,7
7		27	51	59,0%		11	3	11,07	20,91	4,79	51,3%			14,31
8		52	81	69,0%		10	4	16,12	25,11	4,79	46,7%			18,51
9		82	101	72,0%		9	5	22,96	28,28	7,96	-49,6%			21,68
10		102	126	75,0%		8	6	25,5	31,5	11,18	-86,3%			24,9
11		127	252	84,0%		7	7	20,32	40,32	2,37	88,2%			33,72
12		253	501	85,0%		6	8	37,95	75,15	19,93	46,4%			68,55
13		502	751	89,0%		5	9	55,22	82,61	22,45	18,0%			76,01
14		752	1 501	92,0%		4	10	60,16	120,08	14,94	75,1%			113,48
15		1 502	2 001	93,0%		3	11	105,14	140,07	19,95	42,9%			133,47
16		2 002	2 501	94,0%		2	12	120,12	150,06	12,45	58,4%			143,46
17		2 502	3 001	94,5%		1	13	137,61	165,055	14,96	45,5%			158,455
18		3 002	99 999 999	95,0%		1	13	150,1	5000000	4999849,85	0,0%			4999993
19
20
Sheet1

Cell Formulas
Range	Formula
C2	C2	=COUNT(B5:B18)
F5:F18	F5	=IF(Tier-SEQUENCE(ROWS(B5:B18),)>0,Tier-SEQUENCE(ROWS(B5:B18),),1)
G5:G18	G5	=IF(SEQUENCE(ROWS(B5:B18),)<Tier,SEQUENCE(ROWS(B5:B18),),Tier-1)
H5:I18	H5	=B5:B18*(1-$D5:$D18)
J5:J17	J5	=I5-MIN(H6:$H$18)
K5:K18	K5	=1-J5/(I5-H5)
J18	J18	=I18-MIN(B$35:$B36)
N5:N18	N5	=I5#-BYROW(TRANSPOSE(OFFSET(H5#,G5#,,F5#,)),LAMBDA(x,MIN(x)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Tier	=Sheet1!$C$2	F5:G5

 

[Fluff]

How about

=LET(r,ROWS(I5#),MAP(I5#,SEQUENCE(r),LAMBDA(a,b,a-IF(b=r,0,MIN(DROP(H5#,b))))))

 

[ED38]

Thats perfect!
once again!
Thanks a lot Fluff, you saved my day for the 2nd time, very valuable. I appreciate.
There are still excel function I do not know at all...
Thanks for your help and thanks for sharing your expertice

 

[Fluff]

You're welcome & thanks for the feedback.