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:
This behavior is likely caused by how Excel processes dynamic formulas like RANDBETWEEN, LARGE and IF. Functions that generate random values, such as RANDBETWEEN, recalculate every time the sheet is refreshed, edited, or recalculated, which can cause discrepancies in the visible output.
You may try to fix by freezing the random selection, debugging the formula, and replacing the dynamic functions with a helper column