# Changing all numbers above 1 to 1, otherwise unchanged

I have a time series of a lot of numbers, which range from zero to any number. I want to treat all numbers above one as if they are one, but leave the numbers below one unchanged.

For example 2.3456 would become 1.0, but 0.76345 would stay as 0.76345.

What is the simplest way of doing this please? Is there a function that can do it, rather than having to use conditional logic expressions?

I also want to do another separate series of calculations where all numbers below one become one, but numbers above one are unchanged.

edit retag close merge delete

Your question is unclear. Do you want to have it using a formula (to put to another cell, like using =MIN(A1;1))? Or do you need that in-place?

I want to use it as part of a more complex formula. It will refer to the data rather than changing the data. Sorry if I was unclear. The MIN() or MAX() functions seem to be the answer, thank you Mike Kaganski.

Sort by » oldest newest most voted Truncate A1 to 1.0 from top:

=MIN(A1;1)


Truncate A1 to 1.0 from bottom:

=MAX(A1;1)

more