Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGE POSITIVES VALUES TO NEGATIVES | Excel Worksheet Functions | |||
Positives and Negatives | Excel Worksheet Functions | |||
Formula to count only positives in range | Excel Discussion (Misc queries) | |||
Sort ignoring negatives and positives | Excel Worksheet Functions | |||
Formula to average ignoring negatives? | Excel Discussion (Misc queries) |