if between positive and negative statement

orsm6

Active Member
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows
What happens if D5 is outside that range?
Also what happens if D5 is 0?
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

orsm6

Active Member
Joined
Oct 3, 2012
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,889
Messages
5,574,846
Members
412,620
Latest member
sharma7s
Top