Calculating max & min from a range of data indirectly identified

[roscoe]

I have 2 columns of data by year. Column AA are the individual years from 1928-2023. Column AB is the data for that year. I'm currently using that data to randomize a Monte Carlo with =RANDBETWEEN(MIN(ab),Max(AB)) [I know the syntax isn't exact but you should get my point]

What I'd like to do is use a subset of the years to find my min/max values, (ex: 1946-2023). I'd like to do that by putting "1946" in cell "E1" and "2023" in cell "F1". Then I can change the range from which I pull my values directly by simply changing the year values in the 2 cells.

In my mind I need to (1) find the data cell to the right that matches the START year, (2) find the cell to the right that matches the STOP year, (3) use those cells in a range reference in the MIN() and MAX() functions

Pretty sure there's an indirect() function in there somewhere but I can't wrap my head around it.

Thanks

 

[StephenCrump]

Is this what you mean?

	A	B	C	D	E	F	G	H
1				Start	2018		2010	1
2				End	2022		2011	2
3				Random	9		2012	3
4							2013	4
5							2014	5
6							2015	6
7							2016	7
8							2017	8
9							2018	9
10							2019	10
11							2020	11
12							2021	12
13							2022	13
14							2023	14
15							2024	15
16
Sheet1

Cell Formulas
Range	Formula
E3	E3	=XLOOKUP(RANDBETWEEN(E1,E2),G1:G15,H1:H15)

 

[roscoe]

Many thanks but not quite what I was going for. Using your example, I want the Min and Max values from H9-H13 to feed =randbetween(min, max)

 

[roscoe]

Couldn't edit the post...to clarify, using 2018-2022 I get a Min() of 9 and a Max() of 13. In my case the data is not sequential but rather a big scatter ranging from approximately -50 to +50

 

[Cubist]

A couple of ways, try:

Book1
	A	B	C	D	E	F	G	H
1				Start	2018		2010	1
2				End	2022		2011	2
3				Random	9		2012	3
4					11		2013	4
5							2014	5
6							2015	6
7							2016	7
8							2017	8
9							2018	9
10							2019	10
11							2020	11
12							2021	12
13							2022	13
14							2023	14
15							2024	15
Sheet10

Cell Formulas
Range	Formula
E3	E3	=RANDBETWEEN(MINIFS(H1:H15,G1:G15,">="&E1,G1:G15,"<="&E2),MAXIFS(H1:H15,G1:G15,">="&E1,G1:G15,"<="&E2))
E4	E4	=LET(f,FILTER(H1:H15,(G1:G15<=E2)*(G1:G15>=E1)),RANDBETWEEN(MIN(f),MAX(f)))

 

[StephenCrump]

Ha ha, guilty of not reading the question! Sorry.

	A	B	C	D	E	F	G	H
1				Start	2018		2010	10
2				End	2022		2011	11
3				Random	63		2012	12
4							2013	11
5							2014	15
6							2015	6
7							2016	8
8							2017	12
9							2018	99
10							2019	10
11							2020	11
12							2021	12
13							2022	1
14							2023	14
15							2024	15
Sheet1

Cell Formulas
Range	Formula
E3	E3	=LET(f,FILTER(H1:H15,(G1:G15>=E1)*(G1:G15<=E2)),RANDBETWEEN(MIN(f),MAX(f)))

I have shown an extreme example to illustrate the impact of using RANDBETWEEN - it will be influenced by any outliers in the data.

 

[StephenCrump]

Couldn't edit the post...to clarify, using 2018-2022 I get a Min() of 9 and a Max() of 13. In my case the data is not sequential but rather a big scatter ranging from approximately -50 to +50

Yes, we understand that. It was just (my) laziness to generate sequential numbers to illustrate.

 

[roscoe]

I wasn't bashing you, I just wanted to clarify because it looked odd that the Min and Max were also the bookends of the dataset.

 

[roscoe]

=LET(f,FILTER(H1:H15,(G1:G15>=E1)*(G1:G15<=E2)),RANDBETWEEN(MIN(f),MAX(f)))

Thanks...but that didn't work for me. I'm getting results that are waaaaaay out of bounds (all of the data are 2 digit or less but I'm getting 4 digit random numbers). Is there an intermediate calculation I can look at to find the error? I've never used "Let()" or a filter command like that so I don't even know how to troubleshoot

edit: I extracted the Filter step and did that on it's own and it created the correct subset of data. Now I have to figure out why the randbetween calc is not producing the correct result.

 

[roscoe]

Weird, I was able to separate all the steps and the answer works, but a combined version as you built does not.