+ Reply to Thread
Results 1 to 3 of 3

Returning a number from a column based on a results/state in a different coresponding cell

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question Returning a number from a column based on a results/state in a different coresponding cell

    Hi,

    I'm looking to put together a worksheet to use in another workbook relating to when the first goal in a football game is scored. Using the negative binomial distribution function (e.g to get a probability of when a goal hasn't been scored) and means worked out from another worksheet (in the case of the attached I have just pasted the values in and divided by 92 - assuming one minute of extra time per half to get a mean per minute).

    Working this through for Team A (as an example), I have the probabilities per minute of not scoring in Column B, the cumulative of this in Column D and when this exceeds 0.5 (e.g. it is now more likely that a goal has been scored than not scored), returning a GOAL value in column F. Now my query is how can I get this to return a 25 (e.g. from column A) in Cell H3 (e.g. the expected minute in which Team A will score their first goal). I'm sure that this would be possible with an IF formula but can't see a logical way of doing it.

    Any advice would be appreciated.

    P.S. One game means nothing I know - but in the case of this game, Team A scored after 31 minutes so I wasn't too far out.

    First Goal Time Only Workup Book.xls
    Last edited by mrvp; 07-08-2012 at 12:42 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Returning a number from a column based on a results/state in a different coresponding

    hi mrvp, you can try this:
    =INDEX($A$3:$A$95,MATCH("Goal",F$3:F$95,0))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Returning a number from a column based on a results/state in a different coresponding

    Thanks - spot on and solved - I hadn't used the index and match functions before so these are handy to have.
    Last edited by mrvp; 07-08-2012 at 12:43 PM. Reason: clarification

+ 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