Results 1 to 4 of 4

Thread: Alternative to If statements

  1. #1

    Alternative to If statements



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    Im wondering if there is an alternative to if and statements? I have a chart that runs from 2 drop down menus which link to one another, then have radio buttons which lets the user select for specific information.
    I am currently using if and statements but this gets to a point where it can get very large or i will use up the amount of if and statements excel will let me use.

    I have got the chart working so that when the user clicks on the drop down and selects their information, then when they select a radio button it displays the information on the chart, however as stated, its using alot of it and statements and was wondering if there is an alternative?

    My chart is dynamic hence the reason for formulas to change the chart when the user selects their data.

    Many Thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    There usually are alternatives... but we would need to see the formula to determine if so in your case.


  3. #3
    Quote Originally Posted by NBVC View Post
    There usually are alternatives... but we would need to see the formula to determine if so in your case.
    hi,

    the formula i use is if(and(a1="painter1",a2="lincoln",a10,if(and(a1="painter2",a2="birmingham",a1)))) and so on.

    where painter is a drop down and lincoln and birmingham are location drop down menu.

    many thanks.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Are you sure that is written correctly? Reason I ask, is I am looking for a pattern to work with, and in that formula your second IF says to return A1, which would be "painter". Should it be A11? Also what is in A10.

    I am wonder if you can create a table of painters/locations and fill in the table, then use and INDEX/MATCH formula to find the intersect based on your inputs... see here for how to use it: http://www.contextures.com/xlFunctions03.html


    e.g. = INDEX($X$2:$Z$10,MATCH(A1,$W$2:$W$10,0),MATCH(A1,$X$1:$Z$1,0))

    where W2:W10 contain the painters and X1:Z1 contain the locations, X2:Z10 contain the corresponding info.
    Last edited by NBVC; 2013-08-21 at 09:10 PM.


Posting Permissions

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