Help with IF Formula and +ive & -ive

[revillj]

Hey Everyone,

First post for me. Sorry if this is old ground.
Maybe you can help me with an issue i have.

Depending on the value of a cell (E6 in this example) I want to do the following.

If E6 is a negative number then use 1-(100/E6)
If E6 is a positive number then use 1+(E6/100)

Where e6= '-'
=IF(E6<0,1-(100/E6),1+(E6/100))
Answer is incorrect

Where e6= '+'
=IF(E6<0,1-(100/E6),1+(E6/100))
Answer is correct

Any help would be greatly recieved.

Thanks

J

 

[Gerald Higgins]

Where e6= '-'
=IF(E6<0,1-(100/E6),1+(E6/100))
Answer is incorrect

Why exactly is this incorrect ?

Do you perhaps mean

=if(E6< 0,1-(100/(-E6)),1+(E6/100))

???

 

[Jonmo1]

Hey Everyone,

First post for me. Sorry if this is old ground.
Maybe you can help me with an issue i have.

Depending on the value of a cell (E6 in this example) I want to do the following.

If E6 is a negative number then use 1-(100/E6)
If E6 is a positive number then use 1+(E6/100)

Where e6= '-'
=IF(E6<0,1-(100/E6),1+(E6/100))
Answer is incorrect

Where e6= '+'
=IF(E6<0,1-(100/E6),1+(E6/100))
Answer is correct

Any help would be greatly recieved.

Thanks

J

Welcome to the board...

The formula you posted will do exactly what you described.

But, the problem is likely that this

If E6 is a negative number then use 1-(100/E6)

doesn't do what you think it does..

Perhaps you want this instead

=1+(ABS(E6)/100)

This makes it give the same result regardless if E6 is negative or positive.


Hope that helps.

 

[shg]

I think =1+100/abs(e6)