+ Reply to Thread
Results 1 to 4 of 4

Formula to Index if adjacent cell is greater than zero

  1. #1
    Registered User
    Join Date
    02-19-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    2

    Formula to Index if adjacent cell is greater than zero

    I'm currently using an Index formula to randomly assign people (out of a group of 9) to work specific tasks. Here is the formula as it is now:

    =INDEX($BM$3:$BM$11,RANDBETWEEN(1,10))

    I'd like to step this up a bit and make it work for me a little better. I'm looking for a way to have it only assign a person if cell BJ3 is greater than zero (this number is another formula result that calculates how many of each task need to be perofrmed). So far, all of my attempts to combine an IF formula with the one above have resulted in utter failure. I'm using Office 2010 on a work computer, so I am limited as far as add-ins and other work-arounds. Any suggestions you could give for a combined or tweaked fomula would be very much appreciated!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula to Index if adjacent cell is greater than zero

    First, if the randbetween returns 10, that formula will return #Ref!, because there are only 9 values available in BM3:BM11, so you can't get the 10th..
    So that needs to be either
    =INDEX($BM$3:$BM$11,RANDBETWEEN(1,9))
    or
    =INDEX($BM$3:$BM$12,RANDBETWEEN(1,10))
    or
    =INDEX($BM$2:$BM$11,RANDBETWEEN(1,10))

    Anyway....

    You didn't specify what the formula should do if BJ3 is NOT greater than 0, I presume you want it to be left blank?
    Try
    =IF(BJ3>0,INDEX($BM$3:$BM$11,RANDBETWEEN(1,9)),"")

  3. #3
    Registered User
    Join Date
    02-19-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Formula to Index if adjacent cell is greater than zero

    You're absolutely right, I entered the old formula because we used to have a pool of 10 people. Thank you for pointing that out!

    And, YAY, that fomula does exactly what I needed it to! You just saved me a ton of unnecessary work; thank you SO much!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula to Index if adjacent cell is greater than zero

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 07-29-2013, 12:35 PM
  2. Replies: 1
    Last Post: 07-29-2013, 10:48 AM
  3. Copy adjacent cell to next tab if cell is greater than 1
    By markac87 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2013, 04:19 PM
  4. Replies: 6
    Last Post: 04-12-2013, 05:50 AM
  5. Fill Series if adjacent cell value is greater than zero
    By maxflower in forum Excel General
    Replies: 2
    Last Post: 02-12-2012, 07:20 PM

Tags for this Thread

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