The small cases policy usually replaces the figure with a number of characters to identify it maybe less than 10 or 5, etc. For example "<10" or "*Fewer than 5".


In some the data supplied may replace the case figure with fewer than 10 or fewer than 5. This means that we need to generate two different sets of random numbers.

1.Generate a random number between 9 and 5

2.Generate a random number between 4 and 1


This can easily be achieved in Excel using the following formula:

=IF(OR(D2="*Fewer Than 10", D2="*Fewer Than 5"), IF(D2="*Fewer Than 10", RANDBETWEEN(5, 9), IF(D2="*Fewer Than 5", RANDBETWEEN(1, 4), D2)), D2)

In summary, the formula is designed to generate a random number within a specified range if D2 contains certain text, while retaining the original value if those conditions aren't met. This could be useful for simulations or reports where you need random but controlled values based on text conditions.


The figure below is an example of the applied formula:

The cells in Column E contain the formula mentioned above.


If the spreadsheet contains only a single small case policy, apply this formula:

=IF(D2="*Fewer than 5", RANDBETWEEN(1, 4), D2)



Topic last reviewed: 15/09/25