Forum Discussion

cfreddy's avatar
cfreddy
Copper Contributor
Mar 27, 2025
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...
excel
Formulas and Functions
  • gookhancakir's avatar
    gookhancakir
    Mar 28, 2025

    You can separate the RANDBETWEEN into its own helper cell. For example:

    1. In a helper cell (say A1), use:
      =RANDBETWEEN(1, COUNTIFS(F20:F39,"pants", I20:I39,"brown"))
    2. 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!
MoritzG's avatar
MoritzG
Iron Contributor
Mar 27, 2025

It sounds like that the issue is that RANDBETWEEN() recalculates every time the sheet updates (volatile function), causing the result to change even after "Evaluate Formula" shows a different value.

Possible fixes:

  • Store the RANDBETWEEN() in a helper cell and reference the helper cell in your statement
  • Use INDEX() instead of LARGE(), so for your example it would be:
=IF($F$2="bottom",
    INDEX(ROW(B20:B39), RANDBETWEEN(1, COUNTIFS(F20:F39,"pants",I20:I39,"Brown"))),
    "help")


Maybe that helps :) 

Resources