# IF formula help?

1. ## IF formula help?

Hi, new to the forum.

Trying to get my head around spreadsheets, and I've hit a brick wall.

I want a formula which expresses a value as 0,1,3 should the data be less than -0.30, between -.3 and +.3 or greater than +0.30.

So, for example...

If cell E2 is -0.1, I want cell I2 to display 1...because the value is between -.30 and +.30.

If cell E2 is -0.5, I want cell I2 to display 0... because the value is less than -.30.

If cell E2 is 1.1, I want cell I2 to display 3...because the value is greater than +.30.

I'm assuming it's an IF formula, but I keep coming up with errors in the formula.

Any help would be appreciated.

Regards  Register To Reply

2. ## Re: IF formula help?

It can be an IF formula or you could try this

=LOOKUP(E2,{-9.99999999999999E+307,-.30,.30},{0,1,3})  Register To Reply

3. ## Re: IF formula help?

Thank you, works perfectly.

Could you explain how that works for my understanding? Why -9.999999999999E +307?  Register To Reply

4. ## Re: IF formula help?

That is just a very large (in magnitude) negative number. If your numbers are quite small in magnitude, you could use something like -100 instead.

Hope this helps.

Pete  Register To Reply

5. ## Re: IF formula help?

The LOOKUP matches E2 against -9.99999999999999E+307 -.30 and .30
-9.99999999999999E+307 is the lowest negative number Excel can store.

If E2 is >= -9.99999999999999E+307 and < -.30 then the first result is returned from the second pair of brackets, ie 0.
If E2 >= .30 and < .30 then the next number is returned, ie 1
If E2 >= .30 then the third number is returned, ie 3

Think of
-9.99999999999999E+307
-.30
.30

being the start number of a range of numbers

You could also have said

IF(E2>.30,3,IF(E2 >=-0.30,1, 0))  Register To Reply

6. ## Re: IF formula help?

OK I see.

The IF formula at the end is very close to what I was trying originally, but I was trying to put between values for the "1".

By my understanding, the last formula is essentially saying... If E2 is >0,3, award 3. If that is not the case, but it is greater than -0.3, award 1, anything else (i.e. less than -0.3), award 0.

Many thanks again.  Register To Reply

7. ## Re: IF formula help?

Re last formula, yes thats right.  Register To Reply