# problem with getting a random output from range of cells when a criteria is meet

1. ## problem with getting a random output from range of cells when a criteria is meet

Hi

i am wanting to write a formula for school reports. I want to input a result into column 1, which will then depending on the result, choose a random comment from a range of cells in column B
and display that in column C

So "5a", entered into Column A, chooses a comment from say Cell 1 -8 of column B
If "5p" would choose a random comment from Cell 9-16 of column B

any help would be greatly appreciated

2. ## Re: problem with getting a random output from range of cells when a criteria is meet

Try this:

=INDEX(\$B:\$B,IF(A1="5a",RANDBETWEEN(1,8),IF(A1="5p",RANDBETWEEN(9,16),"")))

3. ## Re: problem with getting a random output from range of cells when a criteria is meet

Awesome thank you so much

Is there anyway to alter the formula so that once a random value is assigned to a cell it does not change every time i add another grade? So if "5a" is selected in A1 the value in C1 does not change when I enter a value for A2?

4. ## Re: problem with getting a random output from range of cells when a criteria is meet

You can use VBA solution or copy and paste value

5. ## Re: problem with getting a random output from range of cells when a criteria is meet

This is the formula I am using to generate a random comment from a range of cells. Is there anything I could add to stop it from changing the comments every time I enter a new students score?

=INDEX(\$I:\$I,IF(D17="a",RANDBETWEEN(2,4),IF(D17="b",RANDBETWEEN(5,7),IF(D17="c",RANDBETWEEN(9,11),IF(D17="D",RANDBETWEEN(14,17),"""")))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1