Overcome formula with too many nested fields

[DylangGrimm1]

Does anyone know how to overcome the issue of a formula containing too many nested fields? Or have another suggestion for a formula? What I am trying to do is bassically turn this table into a formula; so when I input a suggested price, it spits out the acceptable price.
[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl67, width: 114, bgcolor: transparent"]Under $1.00
[/TD]
[TD="class: xl68, width: 38, bgcolor: #EEECE1"]0.25
[/TD]
[TD="class: xl68, width: 44, bgcolor: #EEECE1"]0.33
[/TD]
[TD="class: xl68, width: 41, bgcolor: #EEECE1"]0.39
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EEECE1"]0.44
[/TD]
[TD="class: xl68, width: 35, bgcolor: #EEECE1"]0.45
[/TD]
[TD="class: xl68, width: 44, bgcolor: #EEECE1"]0.49
[/TD]
[TD="class: xl68, width: 42, bgcolor: #EEECE1"]0.55
[/TD]
[TD="class: xl68, width: 35, bgcolor: #EEECE1"]0.59
[/TD]
[TD="class: xl68, width: 45, bgcolor: #EEECE1"]0.65
[/TD]
[TD="class: xl68, width: 37, bgcolor: #EEECE1"]0.69
[/TD]
[TD="class: xl68, width: 41, bgcolor: #EEECE1"]0.75
[/TD]
[TD="class: xl68, width: 35, bgcolor: #EEECE1"]0.79
[/TD]
[TD="class: xl68, width: 35, bgcolor: #EEECE1"]0.88
[/TD]
[TD="class: xl68, width: 38, bgcolor: #EEECE1"]0.89
[/TD]
[TD="class: xl68, width: 39, bgcolor: #EEECE1"]0.99
[/TD]
[TD="class: xl66, width: 63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$1.00 - $1.99
[/TD]
[TD="class: xl69, bgcolor: transparent"]1.00
[/TD]
[TD="class: xl71, bgcolor: yellow"]1.09
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.19
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.29
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.39
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.59
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.69
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.79
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.89
[/TD]
[TD="class: xl70, bgcolor: transparent"]1.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Note1: Keep 1.09 but use competitive prices to move in either direction when appropriate
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$2.00 - $2.99
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]2.19
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.29
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.39
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.59
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.69
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.79
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.89
[/TD]
[TD="class: xl70, bgcolor: transparent"]2.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$3.00 - $3.99
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]3.19
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.29
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.39
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.59
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.69
[/TD]
[TD="class: xl70, bgcolor: transparent"]3.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]3.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$4.00 - $4.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]4.19
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.29
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.39
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.59
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.69
[/TD]
[TD="class: xl70, bgcolor: transparent"]4.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]4.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$5.00 - $5.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]5.29
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]5.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]5.59
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]5.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]5.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$6.00 - $6.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]6.29
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]6.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]6.59
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]6.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]6.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$7.00 - $7.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]7.29
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]7.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]7.59
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]7.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]7.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$8.00 - $8.99
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]8.29
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]8.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]8.59
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]8.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]8.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$9.00 - $9.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]9.29
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]9.49
[/TD]
[TD="class: xl70, bgcolor: transparent"]9.59
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]9.79
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]9.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$10.00 - $14.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.49
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$15.99 - $19.99
[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 15.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.49
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Note2: no $15.49
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$20.00 - $29.99
[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 21.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.49
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Note3: no $20.49 **we need to verify 20.99 as there is a cluster of items at that price**
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]$30.00 +
[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 31.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.49
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]xx.99
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Note4: no $30.49 or $30.99
[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula that i was having issues with that has too many nested fields.
=IF(A1<=0.32,0.25,IF(A1<=0.38,0.33,IF(A1<=0.43,0.39,IF(A1<=0.44,0.44,IF(A1<=0.48,0.45,IF(A1<=0.54,0.49,IF(A1<=0.58,0.55,IF(A1<=0.64,0.59,IF(A1<=0.68,0.65,IF(A1<=0.74,0.69,IF(A1<=0.78,0.75,IF(A1<=0.87,0.79,IF(A1<=0.88,0.88,IF(A1<=0.98,0.89,IF(A1<=0.99,0.99,IF(A1<=1.08,1,IF(A1<=1.18,1.09,IF(A1<=1.28,1.19,IF(A1<=1.38,1.29,IF(A1<=1.48,1.39,IF(A1<=1.58,1.49,IF(A1<=1.68,1.59,IF(A1<=1.78,1.69,IF(A1<=1.88,1.79,IF(A1<=1.98,1.89,IF(A1<=2.18,1.99,IF(A1<=2.28,2.19,IF(A1<=2.38,2.29,IF(A1<=2.48,2.39,IF(A1<=2.59,2.49,IF(A1<=2.68,2.59,IF(A1<=2.78,2.69,IF(A1<=2.88,2.79,IF(A1<=2.98,2.89,IF(A1<=3.18,2.99,IF(A1<=3.28,3.19,IF(A1<=3.38,3.29,IF(A1<=3.48,3.39,IF(A1<=3.58,3.49,IF(A1<=3.68,3.59,IF(A1<=3.78,3.69,IF(A1<=3.98,3.79,IF(A1<=4.18,3.99,IF(A1<=0.43,0.39,IF(A1<=4.28,4.19,IF(A1<=4.38,4.29,IF(A1<=4.48,4.39,IF(A1<=4.58,4.49,IF(A1<=4.68,4.59,IF(A1<=4.78,4.69,IF(A1<=4.98,4.79,IF(A1<=5.28,4.99,IF(A1<=5.48,5.29,IF(A1<=5.58,5.49,IF(A1<=5.78,5.59,IF(A1<=5.98,5.79,IF(A1<=6.28,5.99,IF(A1<=6.48,6.29,IF(A1<=6.58,6.49,IF(A1<=6.78,6.59,IF(A1<=6.98,6.79,IF(A1<=7.28,6.99,IF(A1<=7.48,7.29,IF(A1<=7.58,7.49,IF(A1<=7.78,7.59,IF(A1<=7.98,7.79,IF(A1<=8.28,7.99,IF(A1<=8.48,8.29,IF(A1<=8.58,8.49,IF(A1<=0.43,0.39,IF(A1<=8.78,8.59,IF(A1<=8.98,8.79,IF(A1<=9.28,8.99,IF(A1<=0.43,0.39,IF(A1<=9.48,9.29,IF(A1<=9.58,9.49,IF(A1<=9.78,9.59,IF(A1<=9.98,9.79,IF(A1<=10.48,9.99,IF(A1<=10.98,10.49,IF(A1<=11.48,10.99,IF(A1<=11.98,11.49,IF(A1<=12.48,11.99,IF(A1<=12.98,12.49,IF(A1<=13.48,12.99,IF(A1<=13.98,13.49,IF(A1<=14.48,13.99,IF(A1<=15.98,14.99,IF(A1<=16.48,15.99,IF(A1<=16.98,16.49,IF(A1<=17.48,16.99,IF(A1<=17.98,17.49,IF(A1<=18.48,17.99,IF(A1<=18.98,18.49,IF(A1<=19.48,18.99,IF(A1<=19.98,19.49,IF(A1<=21.98,19.99,IF(A1<=22.48,21.99,IF(A1<=22.98,22.49,IF(A1<=23.48,22.99,IF(A1<=23.98,23.49,IF(A1<=24.48,23.99,IF(A1<=24.98,24.49,IF(A1<=25.48,24.99,IF(A1<=25.98,25.49,IF(A1<=26.48,25.99,IF(A1<=26.98,26.49,IF(A1<=27.48,26.99,IF(A1<=27.98,27.49,IF(A1<=28.48,27.99,IF(A1<=28.98,28.49,IF(A1<=29.48,28.99,IF(A1<=29.98,29.49,IF(A1<=31.98,29.99,IF(A1>=31.99,xx.49 or xx.98)))))))))))))))))))))))))))))))))))))))))))))….soon with parenthesis <o></o>

 

[bbott]

It would probably be best to set up a table and then use VLOOKUP. Otherwise, if you want a giant formula, take a look at the LOOKUP function.