# if between positive and negative statement

#### orsm6

##### Active Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### orsm6

##### Active Member
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

##### MrExcel MVP, Moderator
What happens if D5 is outside that range?
Also what happens if D5 is 0?

#### orsm6

##### Active Member
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

##### MrExcel MVP, Moderator

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

• orsm6

#### orsm6

##### Active Member
=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
417
Replies
5
Views
99
Replies
0
Views
263
Replies
10
Views
278
Replies
13
Views
272