New Contributor

what's wrong with this formula?: =IF(D157>=5,"5","D157") I want column E to contain number in Column D if less than or = to 5, if greater than 5 then "5"


Thank you!

6 Replies


"D157" is a literal text string, not a cell reference.



=IF(D157>=5, 5, D157)


or shorter


=MIN(D157, 5)

@Hans Vogelaar 


You are a genius! Thank you very much.


Could I add additional parameters? For example if I wanted to ignore any figures in column D that were less than 1.0 could I add: <1.0,0,


That could be


=MAX(MIN(D157, 5),1)

I probably didn't explain that very well.

Column D is random numbers. I would like column E to: be 0 for numbers less than 1.0, be the number in column D if between 1.0 and 5.0 and be 5.0 for numbers greater than 5.0



Excellent! Thank you very much.