if between positive and negative statement

[orsm6]

Hi - i can get this working in various ways but i can't seem to nail my solution in one formula. trying to an if between formula that if the value of D5 is between -1 and 1 the result is 0.01... BUT

if D5 is a negative number and between -1 and 1 the answer will be -0.01
if D5 is a positive number and between -1 and 1 the answer will be 0.01

the answer is in a column that calculates cost which is why if D is a negative number i need the cost to also be negative result.

TIA

 

[orsm6]

i finally stumbled on my answer.... this seems to work =IF(D1<-1,-0.01,IF(D1<1,0.01,(D1*E1)/F1))

EDIT: no i didn't any value e.g. -2.22 with formula above still changes it to -0.01

 

[Fluff]

What happens if D5 is outside that range?
Also what happens if D5 is 0?

 

[orsm6]

What happens if D5 is outside that range?
Also what happens if D5 is 0?

hi Fluff - so column D there is already a value (it is my quantities column) so if the value in D is NOT between -1 and 1 then the formula calculates the cost based on the value in D.....

if the value in D is between -1 and 1 i want to put either -0.01 or 0.01 in the cost column.

HTH.

 

[Fluff]

How about
=IF(ABS(D1)<1,SIGN(D1)*0.01,(D1*E1)/F1)

 

[orsm6]

How about
=IF(ABS(D1)<1,SIGN(D1)*0.01,(D1*E1)/F1)

that works perfectly thank you. never in a million years would i have gotten that on my own lol

 

[Fluff]

You're welcome & thanks for the feedback.