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:
thanks everyone for the help! I'm taking the answer as it's just volatile. I had read the message in the evaluate dialog that it gets recalculated every time (which is what I want) but the 2nd half, to me, was proof that something weird was happening, since the "final evaluations step" did not match the result in the cell.
I'm going to bypass the problem altogether by putting the randbetween portion into a helper column and using that as the K of my large function.