
1. Re: different multiplier based on popup menu choice
Barry May 8, 2012 10:24 PM in response to BenratxIn 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 popup 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).
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 popup menu choice
Benratx May 11, 2012 3:09 PM in response to BarryBarry, 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 popup menu choice
Wayne Contello May 11, 2012 3:26 PM in response to Benratx=(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 popup menu choice
Barry May 11, 2012 3:42 PM in response to BenratxHi Bentrax,
Syntax for IF is
IF(condition,ifTRUE,ifFALSE)
The first two are required, the third (ifFALSE) is optional. If only the first two arguments are included, IF will do "ifTRUE" 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 "ifFalse" 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 "ifFALSE" 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