1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Vlookup for a game

Discussion in 'Business Applications' started by Jimbo328, Oct 20, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    hi, this is my first post.

    I'm working on a simple excel sheet for class, and I could use some help to finish up. It's created for the game of mahjong. What I'm trying to have it do is:

    When Player1 wins that hand, Player2 3 and 4 will all AUTOMATICALLY show negative numbers to indicate they have lost. I have used vlookup and a drop-down menu.

    Player1 wins --> on the spreadsheet, i select from the drop-down menu "win", the cells on a corresponding sheet will indicate how much player1 has won. What i'm stuck on is how to get player 2 3 and 4 to automatically show that they have lost once i selected player1 as the winner. What's the best formula?

    Thx in advance,
    Tommy
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,572
    First Name:
    Hans
    I don't know how you've built up teh sheet, but if you include a cell next to each player testing whether one of these has one, you can then have the value multiply * -1 and all the values of the ones that have NOT won will become negative.
     
  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Tommy, welcome to the forum. Can you post up a sample?
     
  4. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    hi, i've now attached a sample of the spreadsheet. So, essentially, what i'm trying to do is:

    1) if dave wins a 3hand from brian, i select 3hand in the columns under dave, and lose3hand in the column under brian. The corresponding figures will appear to right of how much money/points is won/lost.
    I currently have that taken care of, but wouldn't mind listening on any recommendations that would make it better.

    2) if dave wins a 3self-win, then automatically the other 3 players will display losing figures with the money/points split 3 ways. So, if dave wins a 3self-win, the points accumulated is 48. but, i want it to automatically display -16 for the other 3 players (48 / -3= -16).

    Thx for taking the time to answer my questions,
    Tommy
     

    Attached Files:

  5. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I got it working, but the formula is a little inelegant..

    =IF(COUNTIF($C7:$F7,"3 self-win")>0,IF(C7="3 self-win",VLOOKUP(C7,WINNINGS,2,0),-16),IF(ISERROR(VLOOKUP(C7,WINNINGS,2,0)),"",VLOOKUP(C7,WINNINGS,2,0)))
     

    Attached Files:

  6. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    thx for the prompt response turbo.
    it looks good so far, but what about for 5selfwin, 7selfwin, etc?

    could i repeat that command for all the different selfwins?

    on a sidenote, what does iserror do in this case?

    Tommy
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Okay I understand now, you need for all scenarios of selfwin to take off 1/3 of the winnings.

    Iserror, will test for illegal characters. eg. #N/A, #Value etc. And can be used with an if statement to trap the error.
     
  8. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    yes, precisely
     
  9. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Try this one in cell I2:

    Code:
    =IF(ISERROR(MATCH("*self*",$C2:$F2,0)),VLOOKUP(C2,WINNINGS,2,0),IF(MATCH("*self*",$C2:$F2,0)=(COLUMN(C2)-2),VLOOKUP(TRIM(CONCATENATE($C2,$D2,$E2,$F2)),WINNINGS,2,FALSE),VLOOKUP(TRIM(CONCATENATE($C2,$D2,$E2,$F2)),WINNINGS,2,FALSE)*-1/3))
    An array formula might be better, but my mind wandered this way and I couldn't focus on array-based ideas. :eek:
    The formula above still needs spaces in the seemingly empty cells.

    Jimmy
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,572
    First Name:
    Hans
    Once you have the formula's doing what you want and have a working version, I could 'translate' it to a vba routine that would do the job, it eliminates the risk of accidentally deleteting formulae.
     
  11. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    hi jimmy, i appreciate the help.
    i've copy and pasted the command into my spreadsheet, but no luck yet.
     
  12. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Tommy, it's difficult to do debugging based on phrases like "no luck yet". You should give error messages or some other clues.

    For me the formula worked, but I admit I started working on turbodante's attachment. The name WINNINGS must be the problem. Replaced it with B48:C73, and uploaded my version (xl2003).

    Jimmy
     

    Attached Files:

  13. Jimbo328

    Jimbo328 Thread Starter

    Joined:
    Oct 20, 2010
    Messages:
    6
    I've tried your version, and it does infact work. My mistake. Ill just work on additional stuff based on your version. Thx for all y'all help
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/957401

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice