+ Reply to Thread
Results 1 to 2 of 2

Excel Formula Length and Nesting Help

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    Indiana, United States
    MS-Off Ver
    Excel 2013
    Posts
    2

    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. #2
    Registered User
    Join Date
    12-09-2015
    Location
    Indiana, United States
    MS-Off Ver
    Excel 2013
    Posts
    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,
    ))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
    )))))))))))))))))))))))))))))))))))))))))))
    ))))))))))))))))))
    Last edited by Please Nerf; 12-09-2015 at 12:45 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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