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.

Excel questions about formulas and negative numbers?

Im trying to set up a simple calculator with excel to help my users for my game i created. I have two questions.

Questions 1:

I am setting up a simple formula, for example, something like this =5000-B8. Lets say cell B8 has something higher than 5000, the answer shows a negative number. However, instead of negative numbers showing, I want to replace it with a word, or N/A. How do i set it up to where i can change the negative numbers into words?

Questions 2:

I want to set up a gradating answer for my users to show how close they are to achieving a merit. I'll give you an example. In the game, the user races so many miles. For each mile, it counts as a point. now, every 250 points the user collects, it gives the user a merit. I want to use excel to calculate how close the user is to another merit. Merits are earned every 250 points the user gains, and it does not reset. The limit the user can earn is 5000 points which means there are 20 merits the user can earn. How would i go about creating a guide to show how close the user is to the next available merit? Would you have to set up an If Then statement? If so, i need step by step instructions on how to do this...

example: The user has 1,317 points. So the next available merit would be at 1,500 points. So this is what the user puts into the cell on the excel spreadsheet:

User input: 1317

(answer) Next Merit in: 183 points

Update:

To Jerry, I do not follow. :/ I need a step by step process. And, how do i make it to where the answer changes once the user passes a certain achievement? like, this users has already passed 5 merits (250,500,750,1000,1250) and is now working on his 6th merit.

Update 2:

To Elmo, How would i go about creating a macro that works with my issue? :D

Update 3:

Its the newest version on Excel. 2007 i think?

4 Answers

Relevance
  • 10 years ago
    Favorite Answer

    1. Easy.

    =IF(5000-B8>0,5000-B8,"Negative")

    2. Also easy. If the number of miles is in A1

    =IF(A1<5000,250*(INT(A1/250)+1)-A1,"No further merits available")

  • Elmo
    Lv 4
    10 years ago

    You didn't mention what version of Excel you are using but in 2003, click on Format>Conditional Formatting that will take care of the first question. The second is more complicated. You would have to create a Macro.

  • John
    Lv 4
    10 years ago

    For Question 1, =IF((5000-B8)<0,"N/a",(5000-B8))

  • Jerry
    Lv 5
    10 years ago

    Use the IF command. Separate your IF, THEN instructions with a comma.

    Two quote symbols together ( "" )means 'Nothing' will be shown in the cell.

    Structure is =IF(cell#=nothing,"" means show nothing)

    Don't forget to use ( and ) to close functions.

Still have questions? Get your answers by asking now.