Need HELP replacing multiple numbers with just one number

Welcome to the board.

Row\Col	A	B	C
1	209	1	B1: =MIN(1, MAX(A1, -1))
2	117	1
3	-218	-1
4	274	1
5	237	1
6	0	0
7	-123	-1
8	-36	-1
9	12	1

Hi,

Personally I'd use a helper column. e.g.

Formula:

Please Login or Register  to view this content.

copy this down, then copy the whole column and paste it back to A1 as Values.

For a third option, I would probably use the =SIGN() function: https://support.office.com/en-us/art...1-2dd0e916a1d8

Something like the following?

Please Login or Register  to view this content.

Originally Posted by MrShorty

For a third option, I would probably use the =SIGN() function:

Of course! The simpler the better. An example to us all to occasionally review and remind ourselves of these little used functions. Well done.

Reckon it depends on what you want the result to be for numbers between -1 and 1.

Originally Posted by shg

Reckon it depends on what you want the result to be for numbers between -1 and 1.

The OP did not say what to do with numbers between +1 and -1. Since all examples given were for "integer" inputs, and nothing was mentioned about fractional values in between +1 and -1, I assumed that all inputs will be integers, so there is nothing between +1 and -1, except for exactly 0. If the possible input set is "all reals", then I would suggest that the OP more carefully define what should be returned in that "undefined" region. Right now, shg's function returns the input value, Richard's function would return empty string "", and Wales MB's would return 0, where mine will return +1 or -1, depending on whether the fraction input is greater than 0 or less than 0.

Originally Posted by shg

Reckon it depends on what you want the result to be for numbers between -1 and 1.

Good point.
The OP seemed to imply only integers so probably there never would be stuff like -0.5 but who knows....

@Extender,

Please do not use the PM facility for raising questions about your thread. That's specifically contrary to our rukes.
Ask them here: that's what it's here for. However don't expect many of us to go to other file hosting sites to see your workbook. Upload it here if you want further help.