+ 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)

Similar Threads

  1. Cell length formula to remove any characters over 31 in length
    By tehkayd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2015, 11:45 AM
  2. Replies: 4
    Last Post: 02-26-2015, 06:10 AM
  3. [SOLVED] Shorten string length if exceeds 31 characters (max sheet name length)
    By r2fro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2014, 05:57 AM
  4. [SOLVED] Finding a formula that will consider length of cell and length of words
    By Mariah B in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2013, 05:39 PM
  5. Replies: 0
    Last Post: 07-10-2013, 11:06 AM
  6. Nesting IF THEN formula correct here? Using Excel '03
    By impatience in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-29-2008, 10:16 AM
  7. [SOLVED] Excel Formula Length
    By Matt in forum Excel General
    Replies: 3
    Last Post: 02-07-2005, 04:06 PM

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