# Keep a cell's value within a range

#### Prowl33

##### New Member
Ive been trying help from other topics I have seen using things like if(and and Sumifs and none of them seem to do what I need them to do

Heres my scenario

if A>\$5000 then I want the returned value to be \$5000, if A<\$0 then I want the returned value to be \$0. Otherwise, I want the value to be A

So.
a=\$6500 I want the cell to return \$5000
a=\$3500 then the cell should return \$3500
a=-\$400 then the cell should return \$0

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### JoeMo

##### MrExcel MVP
Ive been trying help from other topics I have seen using things like if(and and Sumifs and none of them seem to do what I need them to do

Heres my scenario

if A>\$5000 then I want the returned value to be \$5000, if A<\$0 then I want the returned value to be \$0. Otherwise, I want the value to be A

So.
a=\$6500 I want the cell to return \$5000
a=\$3500 then the cell should return \$3500
a=-\$400 then the cell should return \$0

Try:

=IF(A1>5000,5000,IF(A1<0,0,A1))

#### Prowl33

##### New Member
When using that, I get the same issue I keep getting with other formula's. When the value is \$0, it's returning \$5000

#### JoeMo

##### MrExcel MVP
Try:

=IF(A1>5000,5000,IF(A1<=0,0,A1))

#### Prowl33

##### New Member
Think I found my issue. The cell I was pulling the value from, that value was derived from a formula, which had it's own if statement to prevent a less than 0. I didnt think that statement would matter, because I was just referencing the value returned, but seems like it did effect the formula. Thanks for the help

Replies
0
Views
100
Replies
12
Views
922
Replies
8
Views
1K
Replies
10
Views
771
Replies
3
Views
251

1,190,782
Messages
5,982,888
Members
439,804
Latest member
melD94

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back