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:
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!