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

 

[Phuoc]

Try this:

Book1
	D	E	F	G	H
1	Start	2018		2010	44
2	End	2022		2011	56
3	Random	93		2012	38
4				2013	97
5				2014	24
6				2015	83
7				2016	11
8				2017	60
9				2018	39
10				2019	28
11				2020	93
12				2021	61
13				2022	94
14				2023	16
15				2024	66
Sheet1

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

 

[roscoe]

Edit: It works, my bad. When I created a named range for the the data the mouse must have slightly moved because I selected both columns instead of just the second column. I feel embarrassed.

Thanks for the help!!

 

[StephenCrump]

Edit: It works, my bad.

No problem. I'm glad we could help.

But are you sure your methodology is appropriate?

By using RANDBETWEEN(min, max) you're applying a uniform distribution between min and max, rather than looking at what the data is telling you.

Given the data in column A here, for example, are you happy with the simulated results?

Book10
	A	B	C
1	Year	Data	Sim
2	1	10	40
3	2	12	62
4	3	10	86
5	4	100	29
6	5	7	77
7	6	15	43
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=RANDBETWEEN(MIN(B$2:B$7),MAX(B$2:B$7))

 

[roscoe]

I've taken it a different direction. I'm actually using the Mean and Standard deviation to randomize to create a gaussian distribution, Manages outliers way better