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 just offsetting by an amount for some reason, but it's never wrong by the same amount, and is also correct sometimes.

When examining each step of the equation with "evaluate formula" every step is correct, all the way down to the last "if(true,28,"help")"... and then instead of "28" it shows one of the other row numbers that matches.

This is the simplified version, that is behaving in the same manner. The full equation indexes a list of several hundred items with many different categories.

=IF($F$2="bottom",LARGE(IF(I20:I39="brown",IF(F20:F39="Pants",ROW(B20:B39),0),0),RANDBETWEEN(1,COUNTIFS(F20:F39,"pants",I20:I39,"Brown"))),"help")

The issue is not whether the formula finds the correct row#, though, it's that the returned value does not match the calculated value. The formula will run down to =if(true,28,"help") but then return any of the matching rows at random, not always the number it calculates (28 in this example).

I've tried to attach a screenshot and add a table, but it won't publish with them included)

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!

5 Replies

  • cfreddy's avatar
    cfreddy
    Copper Contributor
    Mar 28, 2025

    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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 28, 2025

    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")
)
  • gookhancakir's avatar
    gookhancakir
    Copper Contributor
    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!
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 28, 2025

    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

  • 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