Keep a cell's value within a range

Prowl33

New Member
Joined
Dec 22, 2016
Messages
5
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

Please help! Thanks so much
 

Excel Facts

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,067
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Please help! Thanks so much
Try:

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

Prowl33

New Member
Joined
Dec 22, 2016
Messages
5
When using that, I get the same issue I keep getting with other formula's. When the value is $0, it's returning $5000
 
Upvote 0

Prowl33

New Member
Joined
Dec 22, 2016
Messages
5
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top