1. 05-03-12 08:41 PM #1
    Professor1215
    Improve Everyday
    Professor1215's Avatar Become A Pro!
    Join Date: 11-28-11
    Posts: 216
    Betpoints: 3212

    Microsoft Excel Question

    Let's say Albert Pujols has an average vs RHP of .287 and an average vs LHP of .321.

    In sheet 1 I have:
    In column A titled "Name" I have "Albert Pujols"
    In column B titled "v RHP" I have .287.
    In column C titled "v LHP" I have .321.

    In sheet 2, I want:

    To be able to type the name "Albert Pujols" and "RHP" into another column and it give me ".287".

    Does this make sense, and if so, how do you do it?

    Any help would be greatly appreciated!
    Reply With Quote Reply With Quote
  2. 05-03-12 09:15 PM #2
    sapidoc
    oddsfire
    sapidoc's Avatar Become A Pro!
    Join Date: 03-25-10
    Posts: 1,273
    Betpoints: 712

    http://office.microsoft.com/en-us/ex...005209335.aspx
    Reply With Quote Reply With Quote
  3. 05-03-12 09:25 PM #3
    MonkeyF0cker
    Update your status
    MonkeyF0cker's Avatar Become A Pro!
    Join Date: 06-12-07
    Posts: 12,144
    Betpoints: 1127

    Code:
    =IF(B1="RHP",VLOOKUP(A1,Sheet1!A1:Sheet1!C30,2,FALSE), IF(B1 = "LHP", VLOOKUP(A1,Sheet1!A1:Sheet1!C30,3,FALSE), ""))
    This assumes the range of A1:C30 on Sheet1.
    Reply With Quote Reply With Quote
  4. 05-03-12 10:10 PM #4
    Professor1215
    Improve Everyday
    Professor1215's Avatar Become A Pro!
    Join Date: 11-28-11
    Posts: 216
    Betpoints: 3212

    Quote Originally Posted by MonkeyF0cker View Post
    Code:
    =IF(B1="RHP",VLOOKUP(A1,Sheet1!A1:Sheet1!C30,2,FALSE), IF(B1 = "LHP", VLOOKUP(A1,Sheet1!A1:Sheet1!C30,3,FALSE), ""))
    This assumes the range of A1:C30 on Sheet1.
    I copied that formula and it gives me "FALSE". I made up some random values from A1:C30 on Sheet 1 and I pasted this formula in sheet 2. Not to sound ignorant but I am not sure what I am doing wrong.

    I am looking to type the name, Albert Pujols on Sheet 2 on Column A and RHP on Column B and it give me .287 like I have it set up on Sheet 1

    VLOOKUP is obviously what I need to use, I must just be too ignorant to figure it out.

    Any more suggestions?
    Reply With Quote Reply With Quote
  5. 05-03-12 10:14 PM #5
    MonkeyF0cker
    Update your status
    MonkeyF0cker's Avatar Become A Pro!
    Join Date: 06-12-07
    Posts: 12,144
    Betpoints: 1127

    It gives you "False?" It shouldn't come up with "False" at all.

    Are you sure you copied the formula correctly?

    It works for me...
    Reply With Quote Reply With Quote
  6. 05-03-12 10:16 PM #6
    MonkeyF0cker
    Update your status
    MonkeyF0cker's Avatar Become A Pro!
    Join Date: 06-12-07
    Posts: 12,144
    Betpoints: 1127

    On Sheet2, make A1 the cell where you place the player name. Make B1 the cell where you designate LHP or RHP. And paste the formula into C1.

    Then try it.
    Reply With Quote Reply With Quote
  7. 05-04-12 09:18 AM #7
    That Foreign Guy
    I got sunshine in a bag
    That Foreign Guy's Avatar Become A Pro!
    Join Date: 07-18-10
    Posts: 432
    Betpoints: 3069

    Try naming the data range the batting avg data is in? Maybe your sheets aren't called exactly the same as Monkey's?
    Reply With Quote Reply With Quote
  8. 05-04-12 11:05 AM #8
    Professor1215
    Improve Everyday
    Professor1215's Avatar Become A Pro!
    Join Date: 11-28-11
    Posts: 216
    Betpoints: 3212

    I got it to work, thank you!

    I am now trying to figure out how this formula works for other players with other values.

    I thought it would be as easy as dragging down, but I get a "N/A" when I do that.
    Reply With Quote Reply With Quote
  9. 05-04-12 02:01 PM #9
    thom321
    thom321's Avatar Become A Pro!
    Join Date: 06-17-11
    Posts: 112
    Betpoints: 4983

    In order for the formula to work when you drag it down, you have to make the lookup range reference absolute, meaning Sheet1!A1:Sheet1!C30 should be changed to Sheet1!$A$1:Sheet1!$C$30. See the link below for more detail
    http://spreadsheets.about.com/od/r/g...ute_cell_r.htm

    Also, I highly suggest using a in-cell drop down list, where you select a name from a list of available names, rather than having to type it. Link below has some detailed instructions on how to do it.
    http://www.contextures.com/xldataval02.html
    Reply With Quote Reply With Quote
  10. 05-04-12 04:49 PM #10
    Professor1215
    Improve Everyday
    Professor1215's Avatar Become A Pro!
    Join Date: 11-28-11
    Posts: 216
    Betpoints: 3212

    Thank you very, very much!!!
    Reply With Quote Reply With Quote
Top
« Previous Thread | Handicapper Think Tank | Next Thread »