Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

 
discard

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.