# Excel Formula Length and Nesting Help

1. ## Excel Formula Length and Nesting Help

Hello, I am a student who decided to take on a small project for fun. I ran into a problem so I decided to post on these forums. My project is a blackjack odds calculator. There are 9 hands you can bust off if you hit, 12, 13, 14, 15, 16, 17, 18, 19 and 20. Also, there are 26 different ways you can have cards that omit cards that make you bust from the deck. For instance, if you have a 2 and 10 and the dealer has a 10 there are 2 cards gone that make you bust. I spent a couple hours making a formula that has over 200 IF statements and over 10,000 characters. Does anyone know a way to separate my formula into 9 parts for each hand? And if this is possible, is there anyway to ensure that the formula updates the cell number as it progress down the page? Thank you.

2. ## Re: Excel Formula Length and Nesting Help

This is my ugly, but working formula.
=IF(AND(D2=12,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=12,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=12,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=12,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=12,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=12,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=12,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=12,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=12,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=12,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=12,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=12,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=12,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=12,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=12,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=12,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=12,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=12,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=12,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=12,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=12,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=12,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=12,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=12,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=12,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=12,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=13,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=13,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=13,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=13,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=13,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=13,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=13,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=13,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=13,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=13,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=13,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=13,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=13,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=13,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=13,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=13,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=13,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=13,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=13,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=13,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=13,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=13,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=13,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=13,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=13,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=13,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=14,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=14,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=14,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=14,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=14,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=14,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=14,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=14,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=14,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=14,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=14,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=14,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=14,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=14,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=14,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=14,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=14,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=14,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=14,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=14,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=14,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=14,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=14,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=14,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=14,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=14,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=15,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=15,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=15,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=15,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=15,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=15,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=15,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=15,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=15,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=15,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=15,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=15,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=15,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=15,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=15,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=15,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=15,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=15,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=15,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=15,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=15,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=15,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=15,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=15,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=15,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=15,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=16,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=16,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=16,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=16,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=16,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=16,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=16,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=16,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=16,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=16,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=16,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=16,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=16,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=16,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=16,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=16,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=16,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=16,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=16,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=16,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=16,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=16,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=16,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=16,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=16,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=16,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=17,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=17,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=17,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=17,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=17,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=17,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=17,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=17,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=17,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=17,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=17,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=17,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=17,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=17,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=17,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=17,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=17,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=17,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=17,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=17,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=17,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=17,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=17,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=17,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=17,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=17,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=18,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=18,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=18,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=18,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=18,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=18,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=18,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=18,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=18,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=18,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=18,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=18,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=18,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=18,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=18,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=18,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=18,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=18,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=18,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=18,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=18,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=18,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=18,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=18,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=18,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=18,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=19,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=19,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=19,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=19,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=19,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=19,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=19,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=19,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=19,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=19,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=19,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=19,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=19,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=19,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=19,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=19,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=19,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=19,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=19,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=19,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=19,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=19,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=19,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=19,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=19,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=19,A2+D2=21,B2+D2=21,C2+D2<21),0,

IF(AND(D2=20,A2+D2>21,B2+D2>21,C2+D2>21),3,IF(AND(D2=20,A2+D2>21,B2+D2>21,C2+D2<21),2,IF(AND(D2=20,A2+D2>21,B2+D2>21,C2+D2=21),2,
IF(AND(D2=20,A2+D2>21,B2+D2<21,C2+D2>21),2,IF(AND(D2=20,A2+D2>21,B2+D2<21,C2+D2<21),1,IF(AND(D2=20,A2+D2>21,B2+D2<21,C2+D2=21),1,
IF(AND(D2=20,A2+D2>21,B2+D2=21,C2+D2>21),2,IF(AND(D2=20,A2+D2>21,B2+D2=21,C2+D2<21),1,IF(AND(D2=20,A2+D2>21,B2+D2=21,C2+D2=21),1,
IF(AND(D2=20,A2+D2<21,B2+D2>21,C2+D2>21),2,IF(AND(D2=20,A2+D2<21,B2+D2>21,C2+D2<21),1,IF(AND(D2=20,A2+D2<21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=20,A2+D2<21,B2+D2<21,C2+D2>21),1,IF(AND(D2=20,A2+D2<21,B2+D2<21,C2+D2<21),0,IF(AND(D2=20,A2+D2<21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=20,A2+D2<21,B2+D2=21,C2+D2>21),1,IF(AND(D2=20,A2+D2<21,B2+D2=21,C2+D2<21),0,IF(AND(D2=20,A2+D2<21,B2+D2=21,C2+D2=21),0,
IF(AND(D2=20,A2+D2=21,B2+D2>21,C2+D2>21),2,IF(AND(D2=20,A2+D2=21,B2+D2>21,C2+D2<21),1,IF(AND(D2=20,A2+D2=21,B2+D2>21,C2+D2=21),1,
IF(AND(D2=20,A2+D2=21,B2+D2<21,C2+D2>21),1,IF(AND(D2=20,A2+D2=21,B2+D2<21,C2+D2<21),0,IF(AND(D2=20,A2+D2=21,B2+D2<21,C2+D2=21),0,
IF(AND(D2=20,A2+D2=21,B2+D2=21,C2+D2>21),1,IF(AND(D2=20,A2+D2=21,B2+D2=21,C2+D2<21),0,
))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))
))))))))))))))))))

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1