+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    06-11-2018
    Location
    New Zealand
    MS-Off Ver
    2103
    Posts
    4

    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. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    2,859

    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. #3
    Registered User
    Join Date
    06-11-2018
    Location
    New Zealand
    MS-Off Ver
    2103
    Posts
    4

    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. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    2,859

    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. #5
    Registered User
    Join Date
    06-11-2018
    Location
    New Zealand
    MS-Off Ver
    2103
    Posts
    4

    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),"""")))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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