String of IF formulas using AND Returns False Instead of Needed Values

[d8adiva]

I need the formula to be able to evaluate a cell to find what range of numbers it falls within and then return a single number as a result. My formula is written like this thanks to the help I found on this forum:

=IF(AND(A1>=127,A1<=127.4)19,IF(AND(A1>=127.5,A1<=127.9)20,IF(AND(A1>=128,A1<=128.4)21,IF(AND(A1>=128.5,A1<=128.9)22,IF(AND(A1>=129,A1<=129.4)23,IF(AND(A1>=129.5,A1<=129.9)24,IF(AND(A1>=130,A1<=130.4)25,IF(AND(A1>=130.5,A1<=130.9)26,IF(AND(A1>=131,A1<=131.4)27,IF(AND(A1>=131.5,A1<=131.9)28,IF(AND(A1>=132,A1<=132.4)29,IF(AND(A1>=132.5,A1<=132.9)30,IF(AND(A1>=133,A1<=133.2)31,IF(AND(A1>=133.3,A1<=133.6)32,IF(AND(A1>=133.7,A1<=133.9)33,IF(AND(A1>=134,A1<=134.4)34,IF(AND(A1>=134.5,A1<=134.9)35,IF(AND(A1>=135,A1<=135.2)36,IF(AND(A1>=135.3,A1<=135.6)37,IF(AND(A1>=135.7,A1<=135.9)38,IF(AND(A1>=136,A1<=136.4)39,IF(AND(A1>=136.5,A1<=136.9)40,IF(AND(A1>=137,A1<=137.2)41,IF(AND(A1>=137.3,A1<=137.6)42,IF(AND(A1>=137.7,A1<=137.9)43,IF(AND(A1>=138,A1<=138.4)44,IF(AND(A1>=138.5,A1<=138.9)45,)*0)))))))))))))))))))))))))))

So, the number in cell A1 is 133.6. According to my formula, I would like for the number 32 to be returned in cell B1. I have a string of numbers down the A column for which I want a number between 19 and 45 returned in the B column. The only result I get right now is FALSE for every number right now. It looks like this:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"0\.0"; text-align:center;}--></style>[TABLE="width: 174"]
<!--StartFragment--> <colgroup><col width="87" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]133.6[/TD]
[TD="width: 87, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]132.8[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]134.3[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]130.5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]135.2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]134.3[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]137.9[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]133.8[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]136.5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl63"]132.7[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Any help that anyone can offer is greatly appreciated!! Thank you in advance!!!

 

[Michael M]

Re: String of IF formulas using AND Returns False Instead of Needed Values - Can't Fix It - Please Help!

TRy

=IF(A1<=127.4,19,IF(A1<=127.9,20,IF(A1<=128.4,21,IF(A1<=128.9,22,IF(A1<=129.4,23,IF(A1<=129.9,24,IF(A1<=130.4,25,IF(A1<=130.9,26,IF(A1<=131.4,27,IF(A1<=131.9,28,IF(A1<=132.4,29,IF(A1<=132.9,30,IF(A1<=133.2,31,IF(A1<=133.6,32,IF(A1<=133.9,33,IF(A1<=134.4,34,IF(A1<=134.9,35,IF(A1<=135.2,36,IF(A1<=135.6,37,IF(A1<=135.9,38,IF(A1<=136.4,39,IF(A1<=136.9,40,IF(A1<=137.2,41,IF(A1<=137.6,42,IF(A1<=137.9,43,IF(A1<=138.4,44,IF(A1<=138.9,45,0)))))))))))))))))))))))))))

 

[d8adiva]

Re: String of IF formulas using AND Returns False Instead of Needed Values - Can't Fix It - Please Help!

Wow! I really made that much more difficult than it needed to be! Thank you very much!! You are saving me so much time on my work project! Your quick response is greatly appreciated as well!!

 

[mikerickson]

Re: String of IF formulas using AND Returns False Instead of Needed Values - Can't Fix It - Please Help!

How about =19 +CEILING(A1-127.4, .5)*2