Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to write a formula that will do different things depending on which number range the number inputted to it lies in.

  • If the number is between 0-40 inclusive, I want it to return 0.
  • If the number is between 41-71 inclusive, I want it to return (x-40)*100
  • If the number is between 72-82 inclusive, I want it to return x*100
  • If the number is between 83-94 inclusive, I went it to return x'110

I know vaguely about writing IF statements, but I'm not sure how to write multiple statements or determine what range a value lies in. Any help would be appreciated.

share|improve this question
up vote 2 down vote accepted

Here it is, assuming that any value larger than 94 or less than 0 you want to leave blank:

=IF(A1<0, "",IF(A1<=40, 0, IF(A1<=71, (A1-40*100), IF(A1<=82, A1*100, IF(A1<=94, A1*100,"")))))

If any value is true, then you get the true parameter, and it drops out without testing any further.

share|improve this answer
    
Well I wasn't expecting an answer quite that straightforward, thanks a lot! – victoriah Aug 1 '11 at 21:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .