How can I determine if a number is between two values in OpenOffice Calculator?

Question

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.

Answer 1

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.