DylangGrimm1
New Member
- Joined
- May 1, 2015
- Messages
- 1
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.
<tbody>
</tbody>
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>
| Under $1.00 | 0.25 | 0.33 | 0.39 | 0.44 | 0.45 | 0.49 | 0.55 | 0.59 | 0.65 | 0.69 | 0.75 | 0.79 | 0.88 | 0.89 | 0.99 | |
| $1.00 - $1.99 | 1.00 | 1.09 | 1.19 | 1.29 | 1.39 | 1.49 | 1.59 | 1.69 | 1.79 | 1.89 | 1.99 | Note1: Keep 1.09 but use competitive prices to move in either direction when appropriate | ||||
| $2.00 - $2.99 | 2.19 | 2.29 | 2.39 | 2.49 | 2.59 | 2.69 | 2.79 | 2.89 | 2.99 | |||||||
| $3.00 - $3.99 | 3.19 | 3.29 | 3.39 | 3.49 | 3.59 | 3.69 | 3.79 | 3.99 | ||||||||
| $4.00 - $4.99 | 4.19 | 4.29 | 4.39 | 4.49 | 4.59 | 4.69 | 4.79 | 4.99 | ||||||||
| $5.00 - $5.99 | 5.29 | 5.49 | 5.59 | 5.79 | 5.99 | |||||||||||
| $6.00 - $6.99 | 6.29 | 6.49 | 6.59 | 6.79 | 6.99 | |||||||||||
| $7.00 - $7.99 | 7.29 | 7.49 | 7.59 | 7.79 | 7.99 | |||||||||||
| $8.00 - $8.99 | 8.29 | 8.49 | 8.59 | 8.79 | 8.99 | |||||||||||
| $9.00 - $9.99 | 9.29 | 9.49 | 9.59 | 9.79 | 9.99 | |||||||||||
| $10.00 - $14.99 | xx.49 | xx.99 | ||||||||||||||
| $15.99 - $19.99 | min = 15.99 | xx.49 | xx.99 | Note2: no $15.49 | ||||||||||||
| $20.00 - $29.99 | min = 21.99 | xx.49 | xx.99 | Note3: no $20.49 **we need to verify 20.99 as there is a cluster of items at that price** | ||||||||||||
| $30.00 + | min = 31.99 | xx.49 | xx.99 | Note4: no $30.49 or $30.99 |
<tbody>
</tbody>
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>
