Forum Discussion
Solved
Excel formula not returning calculated result
I have a sheet that is supposed to return to me a random item that matches criteria. The equation functions properly, but the shown result does not match what is calculated. I thought maybe it was ju...
You can separate the RANDBETWEEN into its own helper cell. For example:
- In a helper cell (say A1), use:
=RANDBETWEEN(1, COUNTIFS(F20:F39,"pants", I20:I39,"brown")) - Then update your main formula to:
=IF($F$2="bottom", LARGE( IF(I20:I39="brown", IF(F20:F39="Pants", ROW(B20:B39), 0), 0), A1 ), "help" )
Now, RANDBETWEEN will only calculate once (in A1), and the main formula will use that fixed value — no more unexpected changes during evaluation.
If you want more control, consider using: - INDEX + FILTER + SORTBY + RANDARRAY combo (if you’re on Excel 365).
- Or use a macro (VBA) to only recalculate when a button is clicked.
Let me know if you’d like help rewriting the formula using one of those approaches!
- In a helper cell (say A1), use:
As Kidd_Ip explained, RANDBETWEEN() is volatile function which is recalculated when we do any other calculation. Includes with F9. With F9 when we check random number it evaluates this function and show some number. Next we calculate entire formula, with that random number is recalculated and we have another value.
To check you formula works correctly you may use LET() function. It pre-evaluates values on each step and uses results on next steps. With
=LET(
n, RANDBETWEEN( 1, COUNTIFS( F20:F39, "pants", I20:I39,"Brown" ) ),
ind, ( I20:I39="brown" )*( F20:F39="Pants" ) * ROW(B20:B39),
IF( $F$2="bottom", LARGE( ind, n ) & "=" & n, "help")
)random number is fixed within LET() and we may see what result is correct
Your final formula could be
=LET(
n, RANDBETWEEN( 1, COUNTIFS( F20:F39, "pants", I20:I39,"Brown" ) ),
ind, ( I20:I39="brown" )*( F20:F39="Pants" ) * ROW(B20:B39),
IF( $F$2="bottom", LARGE( ind, n ), "help")
)