Ask Your Question

Changing all numbers above 1 to 1, otherwise unchanged

asked 2019-03-25 12:31:54 +0100

galaxian gravatar image

updated 2019-03-25 12:35:06 +0100

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 flag offensive 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?

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-25 12:34:17 +0100 )edit

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.

galaxian gravatar imagegalaxian ( 2019-03-25 13:03:45 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-03-25 12:37:02 +0100

Truncate A1 to 1.0 from top:


Truncate A1 to 1.0 from bottom:

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-25 12:31:54 +0100

Seen: 32 times

Last updated: Mar 25 '19