Alternative to nested IF functions in excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Tackleberry
    SBR Sharp
    • 12-01-10
    • 441

    #1
    Alternative to nested IF functions in excel
    As I'm starting to do more and more with excel I need to find an alternative to using nested IF functions. Debugging nightmare and far from efficient I am sure.

    What other viable options are available to me?

    Thank you very much in advance.
  • ejfel01
    SBR Sharp
    • 06-17-10
    • 301

    #2
    Instead of using obscure nested "if" statements, one could always use the built-in "and" logical operator.
    It is used as =and(first_parameter; second_parameter, ...). "And" returns true, if all of its parameters are true.
    =if(and(p1;p2;case when true;case when false).
    Comment
    • mrtomk
      SBR High Roller
      • 02-24-11
      • 105

      #3
      Something like the above should work fine. Anything specific you are trying to achieve?
      Comment
      • Tackleberry
        SBR Sharp
        • 12-01-10
        • 441

        #4
        Originally posted by mrtomk
        Something like the above should work fine. Anything specific you are trying to achieve?
        I will give a generic example of where using the IF function is become a pain for me right now.

        I will have a column full of the probabilities of a specific outcome occuring (ie points scored by a team). I then use the RAND function and then nested IF functions to determine the outcome for each trial.
        Comment
        • RickySteve
          Restricted User
          • 01-31-06
          • 3415

          #5
          Read this.
          Comment
          • Tackleberry
            SBR Sharp
            • 12-01-10
            • 441

            #6
            That looks like exactly what I need to continue to improve. Cheers RickySteve.
            Comment
            • mrtomk
              SBR High Roller
              • 02-24-11
              • 105

              #7
              Wow, that books looks like the absolute ideal resource for this. Well done Ricky Steve. Sorry I cant be of more help, TackleBerry. I'm usually decent with ways to work around specific problems, but its tricky to define or put down in writing some better, general way of thinking about it, at least for me. Presumably the author of the above book does this well. If there is anything specific that is causing issue in the meantime, feel free to post and I'll have a crack. Best of luck with it...
              Comment
              • That Foreign Guy
                SBR Sharp
                • 07-18-10
                • 432

                #8
                For the specific example you give it sounds like you could use a vlookup with range = true.
                Comment
                • Wrecktangle
                  SBR MVP
                  • 03-01-09
                  • 1524

                  #9
                  Originally posted by That Foreign Guy
                  For the specific example you give it sounds like you could use a vlookup with range = true.
                  I usually employ vlookup for problems like this.
                  Comment
                  • Tackleberry
                    SBR Sharp
                    • 12-01-10
                    • 441

                    #10
                    vlookup looks ideal. Thanks guys.
                    Comment
                    SBR Contests
                    Collapse
                    Top-Rated US Sportsbooks
                    Collapse
                    Working...