Formula Working With Positives and Negatives
 

I am trying to create a formula the looks at the value of a cell and
determines if it is with a number range. Depending on the number range the
formula result will be that number multiplied by a number in another cell.

My Value range is between +5 and -30 anything above +5 is equal to 105 and
anything beyond -30 is equal to 0.

If the Value of A1 is Greater than 5 the it is 105
If the Value of A1 is 0 then 100
If the Value of A1 is equal to or in between 1 and 5 OR -1 and -5 then it'll
be that value times 0.5
if the Value of A1 is equal to or in between -6 and -10 then it'll be that
value times 1
if the Value of A1 is equal to or in between -11 and -20 then it'll be that
value times 2
if the Value of A1 is equal to or in between -21 and -30 then it'll be that
value times 3
If the Value of A1 is equal to or beyond -31 then 0


Here are the formulas I have tried on my own so far.....

=IF(OR(AND(A1"0",A1<"10"),AND(A1="-1",A1<="-5")),A1*H4+100,IF(AND(A1="-6",A1<="-10"),A1*H5+100,IF(AND(A1="-11",A1<="-20"),A1*H6+100,IF(AND(A1="-21",A1<="-30"),A1*H7+100&IF(A1<="-31","0",IF(A1=0,"100",IF(A1="10","105","")))))) )


=IF(A1<"-30",0%,IF(A1<"-21",(A1*H7)+100%,IF(A1<"-11",(A1*H6)+100%,IF(A1<"-6",(A1*H5)+100%,IF(A1<"-1",(A1*H4)+100%,IF(A1="0",100%,IF(A1"0",(A1*H4)+1 00%,IF(A1"+10",105%))))))))


=IF($A1<"+10",($A1*$H$4)%+100%,IF($A1<"+1",100%,IF ($A1="0",100%,IF($A1<"-5",($A1*$H$5)%+100%,IF($A1<"-10",($A1*$H$6)%+100%,IF($A1<"-20",($A1*$H$7)%+100%,IF($A1<"-30","0%","105%")))))))


I cannot seem to get it right. Can anyone help me figure out how I can nail
this down and make it work?

Thank You Very Much In Advance,
Rob

Formula Working With Positives and Negatives
 

Here is the easy solution

=IF(A1=0,100,IF(A1<=-31,0,IF(A1<=-21,3*A1,IF(A1<=-11,2*A1,IF(A1<=-6,A1,IF(A1<=5,0.5*A1,105))))))

"Rob" wrote:

I am trying to create a formula the looks at the value of a cell and
determines if it is with a number range. Depending on the number range the
formula result will be that number multiplied by a number in another cell.

My Value range is between +5 and -30 anything above +5 is equal to 105 and
anything beyond -30 is equal to 0.

If the Value of A1 is Greater than 5 the it is 105
If the Value of A1 is 0 then 100
If the Value of A1 is equal to or in between 1 and 5 OR -1 and -5 then it'll
be that value times 0.5
if the Value of A1 is equal to or in between -6 and -10 then it'll be that
value times 1
if the Value of A1 is equal to or in between -11 and -20 then it'll be that
value times 2
if the Value of A1 is equal to or in between -21 and -30 then it'll be that
value times 3
If the Value of A1 is equal to or beyond -31 then 0


Here are the formulas I have tried on my own so far.....

=IF(OR(AND(A1"0",A1<"10"),AND(A1="-1",A1<="-5")),A1*H4+100,IF(AND(A1="-6",A1<="-10"),A1*H5+100,IF(AND(A1="-11",A1<="-20"),A1*H6+100,IF(AND(A1="-21",A1<="-30"),A1*H7+100&IF(A1<="-31","0",IF(A1=0,"100",IF(A1="10","105","")))))) )


=IF(A1<"-30",0%,IF(A1<"-21",(A1*H7)+100%,IF(A1<"-11",(A1*H6)+100%,IF(A1<"-6",(A1*H5)+100%,IF(A1<"-1",(A1*H4)+100%,IF(A1="0",100%,IF(A1"0",(A1*H4)+1 00%,IF(A1"+10",105%))))))))


=IF($A1<"+10",($A1*$H$4)%+100%,IF($A1<"+1",100%,IF ($A1="0",100%,IF($A1<"-5",($A1*$H$5)%+100%,IF($A1<"-10",($A1*$H$6)%+100%,IF($A1<"-20",($A1*$H$7)%+100%,IF($A1<"-30","0%","105%")))))))


I cannot seem to get it right. Can anyone help me figure out how I can nail
this down and make it work?

Thank You Very Much In Advance,
Rob

Formula Working With Positives and Negatives
 

WOW! You made that seem sooo darn Simple!

I Thank You Completely!



"Joel" wrote:

Here is the easy solution

=IF(A1=0,100,IF(A1<=-31,0,IF(A1<=-21,3*A1,IF(A1<=-11,2*A1,IF(A1<=-6,A1,IF(A1<=5,0.5*A1,105))))))

"Rob" wrote:

I am trying to create a formula the looks at the value of a cell and
determines if it is with a number range. Depending on the number range the
formula result will be that number multiplied by a number in another cell.

My Value range is between +5 and -30 anything above +5 is equal to 105 and
anything beyond -30 is equal to 0.

If the Value of A1 is Greater than 5 the it is 105
If the Value of A1 is 0 then 100
If the Value of A1 is equal to or in between 1 and 5 OR -1 and -5 then it'll
be that value times 0.5
if the Value of A1 is equal to or in between -6 and -10 then it'll be that
value times 1
if the Value of A1 is equal to or in between -11 and -20 then it'll be that
value times 2
if the Value of A1 is equal to or in between -21 and -30 then it'll be that
value times 3
If the Value of A1 is equal to or beyond -31 then 0


Here are the formulas I have tried on my own so far.....

=IF(OR(AND(A1"0",A1<"10"),AND(A1="-1",A1<="-5")),A1*H4+100,IF(AND(A1="-6",A1<="-10"),A1*H5+100,IF(AND(A1="-11",A1<="-20"),A1*H6+100,IF(AND(A1="-21",A1<="-30"),A1*H7+100&IF(A1<="-31","0",IF(A1=0,"100",IF(A1="10","105","")))))) )


=IF(A1<"-30",0%,IF(A1<"-21",(A1*H7)+100%,IF(A1<"-11",(A1*H6)+100%,IF(A1<"-6",(A1*H5)+100%,IF(A1<"-1",(A1*H4)+100%,IF(A1="0",100%,IF(A1"0",(A1*H4)+1 00%,IF(A1"+10",105%))))))))


=IF($A1<"+10",($A1*$H$4)%+100%,IF($A1<"+1",100%,IF ($A1="0",100%,IF($A1<"-5",($A1*$H$5)%+100%,IF($A1<"-10",($A1*$H$6)%+100%,IF($A1<"-20",($A1*$H$7)%+100%,IF($A1<"-30","0%","105%")))))))


I cannot seem to get it right. Can anyone help me figure out how I can nail
this down and make it work?

Thank You Very Much In Advance,
Rob