Excel

Copper 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

@TRM353 

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

Use

 

=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,

@TRM353 

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

@TRM353

=IF(D157<1,0,MIN(D157,5))

Excellent! Thank you very much.