Limiting the range in a cell (Excel Help)

Factual Questions
1

Maybe I’m just using the wrong search terms both in google and the help file, but I can’t figure out how to do this in excel.

I need a way to limit cells between 0 and 35. In other words, if the value of a calculation falls below 0, then the value displayed by the cell is 0. At the same time, if the value of a calculation is above 35, then the value displayed by the cell needs to be 35.

Any idea how to do this? I’m working with a sizable spreadsheet and really don’t want to have to go through and manually change every negative number to 0 and every number greater than 35 to 35 every time new data is introduced.

Please help! The sooner I get this done, the sooner I get to go out and enjoy my Saturday!

2

Off the top of my head (been awhile since I opened Excel):


=AND(A1>=0,A1<=35)

where A1 is your value, reference cell, or formula

Edit:

The above will return TRUE/FALSE, so you’d probably want to mix it with IFs instead:


=IF(IF(A1>=0,A1,0)<=35,IF(A1>=0,A1,0),35)
3

The if-then-else formula suggested by fubbleskag is close. I’d do it like this:

=if(a1<=0,0,if(a1>=35,35,a1))

So that if a1 is less than or equal to 0, the number will be transformed to 0, if it is 35 or greater it will be 35, and lastly if it is between 0 and 35 it will remain as the original number.

4

Duke, Thank you so much!!! It works:)

5

Thank you - I figured there was a more elegant way to do what I was doing, but it wasn’t obvious to me.

6

There is also a MIN (return the smaller of these number) function and a MAX (the larger) function. Put them together to get this:

=MAX(0,MIN(35,A1))

7

One of these days Excel is going to have a BETWEEN function for this very problem, I suppose. It would be rather useful for many applications…