
1. Re: Looking for help with If/Then
Wayne Contello Feb 8, 2013 9:35 AM in response to Elissa Cthis can be handled as nested if()... but that gets hard to read:
assuming that the Current (Amperage) is entered in cell A2:
B2=IF(A2 >26, 40, IF(A2 >17, 30, IF(A2 >12, 20, 15) ) )
this means in cell B2 and the formula "=IF(A2 >26, 40, IF(A2 >17, 30, IF(A2 >12, 20, 15) ) )" without the doublw quotes:
an alternate way is to use a lookup table like:
In this case you would make a table (named "BreakerSize" as shown) then enter the thresholds... first row is a header row.
In the table on the left I have included this new method below the original one I presented in cell B3:
B2=VLOOKUP(A20.000000001,BreakerSize :: A:B, 2, 1)
I was unable to get this to work correctly without subtracting 0.0000000001 from A2 in the argument to VLOOKUP(). I don't think it should be necessary but on my machine and version of Numbers this is what made it work as described int he help.

2. Re: Looking for help with If/Then
Badunit Feb 8, 2013 10:20 AM in response to Wayne ContelloNothing to do with your machine or version. From VLOOKUP's perspective, the table says if >=0 and <12 then 15A breaker, if >=12 and <17 then 20A breaker, etc. which isn't exactly the expression required. Either the "working current" numbers in the table need to be bumped up by a fractional amount or you need to do what you did in the VLOOKUP.

3. Re: Looking for help with If/Then
Jerrold Green1 Feb 8, 2013 11:19 AM in response to Elissa CA fairly compact expression that doesn't require the small offset uses the Match function to locate the proper row of a lookup table.
Here's an example:
That expression, should you wish to copy it, is:
=INDEX(Breakers, MATCH(A, Breakers :: A, 1))
Regards,
Jerry


5. Re: Looking for help with If/Then
Elissa C Feb 8, 2013 3:29 PM in response to Wayne ContelloLooking for a bit of fine tuning....The actual calculation for my sample is 16.875 which "should" generate a "20" in the cell. A "25" is showing up. Here is my function:
=IF((Table 5 :: A1*1.25) >26, 40, IF((Table 5 :: A1*1.25) >22, 30, IF((Table 5 :: A1*1.25) >17, 25, IF((Table 5 :: A1*1.25) >12, 20, 15)))) What do you think?
So, to simplify: if >26 answer = 40
if >22 = 30
if >22 = 25
if >17 = 20
if 12 or less = 15
Thanks in advance for you assistance!
Elissa

6. Re: Looking for help with If/Then
Jerrold Green1 Feb 8, 2013 3:31 PM in response to Elissa CI doubt that for this purpose there will be fractional amperes entered, so the following would also likely do the job:
I just bumped the current values in the header of the lookup table.
Jerry

7. Re: Looking for help with If/Then
Badunit Feb 8, 2013 4:05 PM in response to Wayne ContelloWayne Contello wrote:
I think this does NOT match the behavior of the documentation:
Isn't that what it is doing? When you lookup the value 12, for instance, you really want the value next to 0 but since it found an exact match at 12 it gives you the value next to 12.

8. Re: Looking for help with If/Then
Wayne Contello Feb 8, 2013 4:01 PM in response to BadunitIf the search_val = 26 and the lookup table has rows like:
17, 30
26, 40
then I would expect the return value would be 30 because the largest toprow value that is less than the search value is 17... the 26 in the look up table is the toprow value
toprow value < search_val > 17 < 26 is true
toprow value < search_val > 26 < 26 is NOT true

9. Re: Looking for help with If/Then
Badunit Feb 8, 2013 4:08 PM in response to Wayne ContelloBut there is an exact match at 26. The function description says If there's no exact match then return the largest top row value less than the search value. The assumption would be that if there is an exact match then return the value for the exact match.

10. Re: Looking for help with If/Then
Wayne Contello Feb 8, 2013 4:37 PM in response to Wayne ContelloI missed that part. Thank you.