Formula Help

[BillLillard]

Hey guys, I'm not well versed with this software, so please don't mind my ignorance to how it works. Most of what I've needed to figure out I've been able to google and put together, but I'm stumped on getting it to do one thing.

I need an input number to convert to another number. I would like to make these conversions:

5 to 5
4.5 to 4
4.0 to 3
3.5 to 2
3.0 to 1
2.5 to -1
2.0 to -2
1.5 to -3
1.0 to -4
0.5 to -5

So if I enter a 2 in one cell, the next cell over will spit out -2, accordingly. Is this easily doable?

Thanks in advance for any help.
Bill

[robleyd]

G'day, and welcome to the community forum.

Have a look at the VLOOKUP() function.

[Villeroy]

You should be clear about the return value for any other number. What about 999, 13, 4.1, 3.2, -13 to mention a few ?

[keme]

This will output what you ask for, assuming that your input cell is A1.

Code: Select all

=2*A1-5-IF(A1<=2.5;1;0)

Beware of invalid input. Cf. Villeroy's advice above.

[Lupp]

In addition to the comment by Villeroy I would dare to suggest that the OQ checks again if not the omission of a zero result is a logical error. Generally scales allowing for both positive and negative values have also a point zero. The numbering of years on a historical time scale should be regarded an inglorious exception, historic but outdated.