Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Microsoft Excel If Statement?

I'm trying to create a "guide" for a game I play on the internet to help the users of that game play more efficiently.

A little back story of the game and why I'm having troubles with creating a game guide in excel for this game. It's a text-based game that relies on it's users to create/initiate missions/events through out the game. There are about 200-300 different things these players can do, all of which is recorded on a "player stats" page. For example, one of the things the user can do is "attack" other people online and if they win, their "Attacks Won" stat goes up by one point. There are multiple milestones for this specific player stat that the player earns "merits" as the successfully attack and win more fights. There is a merit awarded to the player for 5, 20, 50, 100, 250, 500, 1000, 2500, 5000, 10000, and 25000 successful Attacks Won. Each merit you can earn is named something different for each milestone. The game guide I'm trying to create will tell the player how many more hits are required till their next achievable merit in that specific stat.

Here's what I need help with:

There is an input area in excel for the user to type in their stats, but I need the output to read appropriate according to the player's specific stat they entered into the input box.

For example, lets say the player has 5,102 attacks and they want to know how many more attacks they need to do till they get their next achievable merit in the "Attacks Won" stat. Once the player enters "5,102" into the input box, I want the output box to say, "You need 4,898 more successful attacks to get the Idolized Merit."

So I need the output to have two variables, one being the number they have left, and the other being the name of merit they are currently working towards. I tried using an If statement in the output box, but I soon realized that I wasn't able to put in two variables with the words I wanted to say. I need to figure out a way to incorporate this type of formula into the output box.

I would consider myself somewhat new at Excel, I'm still learning the basics. Any advice or maybe a good tutorial website would seriously help me out in this situation. If you have a better idea of how to go about this, I'm all ears as well!

1 Answer

Relevance
  • Greg G
    Lv 7
    8 years ago
    Favorite Answer

    You'll need to adjust ranges in these formulas to match your specifics.

    First, create a small table with the values 0, 5, 20, 50, 100, 250, 500, 1000, 2500, 5000, 10000, and 25000 down one column. I'm using J1:J12 for this. Put your description of each total in the range K1:K12.

    Now, I input the number of successful attacks in B1

    Put this formula in C1: =LOOKUP($B$1, $J$1:$J$12, $K$1:$K$12)

    So, if you enter, 6 (through 19) successful attacks, this will show the value for 5 attacks.

    Now for the fun formula. I entered this in D1:

    ="You need " & IF($B$1<$J$1, $J$1, IF($B$1>=$J$12, #N/A, INDEX($J$1:$J$12, MATCH($B$1, $J$1:$J$12)+1))) - $B$1 & " more successful attacks to get the " & LOOKUP(IF($B$1<$J$1, J1, IF($B$1>=$J$12, #N/A, INDEX($J$1:$J$12, MATCH($B$1, $J$1:$J$12)+1))), $J$1:$J$12, $K$1:$K$12) & "."

    This will tell the user what they need to make it to the next level.

    You can also put this formula in L1 and copy down to L12:

    =IF($B$1>J1, "You are beyond this level", "You need " & J1-$B$1 & " more successful attacks to get the " & K1 & ".")

    This will show how much is required to reach that particular level.

Still have questions? Get your answers by asking now.