4 Replies Latest reply: May 11, 2012 3:42 PM by Barry
Benratx Level 1 Level 1 (0 points)

I am trying to set up a form to calculate formulations on my IPad. What I would like to do is be able to apply a different to multiplier to a cell value depending on which option is chosen from a pop-up menu.


Ideally it would work like this:

A person selects which product they are making from a pop up list. They then enter a weight in the next field, which would mulitplied by a different amount depending on which choice they made in pop-up list.  Is this possible?

 

Thanks for your help,

Ben


iPad
  • 1. Re: different multiplier based on pop-up menu choice
    Barry Level 7 Level 7 (29,180 points)

    In Numbers '09 (for Mac OS X), the topic of this community, you'd use one of the Lookup functions to lookup the percentage associated with each product. In the example below, I've placed only the choices shown into the pop-up menus in column A of the table on the left.

     

    RThe default chooice, "Choose" causes the formula to throw an error, which is caught by IFERROR, which returns an empty string (displays as a blank cell).

     

    Picture 9.png

    Formula:

    C2, and filled down: =IFERROR(B*LOOKUP(A,Table 2 :: $A,Table 2 :: $B),"")

     

    LOOKUP and IFERROR are discussed, with definitions, syntax and examples, in the iWork Formulas and Functions User Guide. The link will take you to the download page for this guide and others for the iWork applications.

     

    Checked in numbers '09 (Mac). Not guaranteed to work in Numbers for iOS, as I'm unable to check it there.

     

    Regards,

    Barry

  • 2. Re: different multiplier based on pop-up menu choice
    Benratx Level 1 Level 1 (0 points)

    Barry, thanks for trying to help, but I can't get it to work in the IPad Version. in searching the forums i found this thread https://discussions.apple.com/message/16767959#16767959 where you helped someone else deal with a similar issue. I had originally thought that I could use the IF function to do this but I keep getting a syntax error.

     

    I've tried replicating the way you have it written and I still get the error. It works as desired with the first option, but as soon as I try to add additional if's it give me the error. Here is how I have it written, any help would be appreciated:

     

    =(IF D2="Option 1", C2*29.6)

    when entered like this it works for option 1 and returns false for the other options. That's great, but when I try to add aditional options I get a syntax error. Here is how I have been trying to do it, can you see what I am doing wrong?

     

    =(IF D2="Option 1", C2*29.6), IF(D2="Option 2", C2*22)

  • 3. Re: different multiplier based on pop-up menu choice
    Wayne Contello Level 6 Level 6 (13,615 points)

    =(IF D2="Option 1", C2*29.6), IF(D2="Option 2", C2*22)

     

    is not proper syntax.

     

    the IF() function is formed as follows:

     

    IF(<BOOLEAN EXPRESSION>, <WHAT TO RETURN WHEN TRUE>, <WHAT TO RETURN WHEN FALSE>)

     

    I think your formula should be:

    =IF(D2="Option 1", C2*29.6,   IF(D2="Option 2", C2*22, "")  )

     

    this will check to see if D2 is equal to "Option 1".  If it is it will return C2 * 29.6.  If it is NOT then there is another embedded if statement which checks for D2="Option 2".

     

    If there are very many options I would think of another way to do this as nesting IF() functions is ok but it gets difficult to read (in my opinion)

     

    The Numbers Users' Guide will be of great help to you:

    http://support.apple.com/manuals/#productivitysoftware

     

    Regards,

    Wayne

  • 4. Re: different multiplier based on pop-up menu choice
    Barry Level 7 Level 7 (29,180 points)

    Hi Bentrax,

     

    Syntax for IF is

     

    IF(condition,if-TRUE,if-FALSE)

     

    The first two are required, the third (if-FALSE) is optional. If only the first two arguments are included, IF will do "if-TRUE" when the condition is TRUE, and will return FALSE, as you've noted, when the condition is false. I prefer to always use all three arguments, as it makes the process of adding nested IFs easier—each new IF replaces the "if-False" argument of the previous IF.

     

    You want the formula to perform a second test IF the first returns FALSE. To accomplish that, the second test must be entered as the "if-FALSE" argument to the first IF statement. Here's the revision needed:

     

    =(IF D2="Option 1", C2*29.6), IF(D2="Option 2", C2*22)  (your formula from above)

    =(IF D2="Option 1", C2*29.6, IF(D2="Option 2", C2*22))  (revised formula)

     

    =(IF D2="Option 1", C2*29.6, IF(D2="Option 2", C2*22,"no fit"))  (further revision with both IFs getting three arguments)

     

    To add a third option, IF(D2+"Option 3", C2*17,"no fit"), this new IF is easily pasted into the existing formula in place of "no fit"

     

    Regards,

    Barry