Calculating max & min from a range of data indirectly identified

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,062
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Is this what you mean?

ABCDEFGH
1Start201820101
2End202220112
3Random920123
420134
520145
620156
720167
820178
920189
10201910
11202011
12202112
13202213
14202314
15202415
16
Sheet1
Cell Formulas
RangeFormula
E3E3=XLOOKUP(RANDBETWEEN(E1,E2),G1:G15,H1:H15)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
A couple of ways, try:
Book1
ABCDEFGH
1Start201820101
2End202220112
3Random920123
41120134
520145
620156
720167
820178
920189
10201910
11202011
12202112
13202213
14202314
15202415
Sheet10
Cell Formulas
RangeFormula
E3E3=RANDBETWEEN(MINIFS(H1:H15,G1:G15,">="&E1,G1:G15,"<="&E2),MAXIFS(H1:H15,G1:G15,">="&E1,G1:G15,"<="&E2))
E4E4=LET(f,FILTER(H1:H15,(G1:G15<=E2)*(G1:G15>=E1)),RANDBETWEEN(MIN(f),MAX(f)))
 
Upvote 0
Ha ha, guilty of not reading the question! Sorry.

ABCDEFGH
1Start2018201010
2End2022201111
3Random63201212
4201311
5201415
620156
720168
8201712
9201899
10201910
11202011
12202112
1320221
14202314
15202415
Sheet1
Cell Formulas
RangeFormula
E3E3=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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
=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.
 
Last edited:
Upvote 0
Weird, I was able to separate all the steps and the answer works, but a combined version as you built does not.
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top