Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 1 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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.