10 Replies Latest reply: Feb 8, 2013 4:37 PM by Wayne Contello
Elissa C Level 1 Level 1 (0 points)

Hi Gurus,

 

I am in the PhotoVoltaic Industry and am looking for a way to apply the correct amperage for the breaker in a circuit.  For 12 amps or less I will use a 15 amp breaker, for >12 - 17 will use a 20 amp breaker, for >17 - 26 a 30 amp breaker and for >26 - 35 a 40 amp breaker.  Can anyone help me with the function I will need to use?

 

Many thanks!

Elissa


Pages, Mac OS X (10.7.5)
  • 1. Re: Looking for help with If/Then
    Wayne Contello Level 6 Level 6 (13,615 points)

    this 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:

    Screen Shot 2013-02-08 at 11.20.49 AM.png

     

    an alternate way is to use a lookup table like:

     

    Screen Shot 2013-02-08 at 11.31.59 AM.png

     

    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(A2-0.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 Level 6 Level 6 (10,815 points)

    Nothing 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 Level 7 Level 7 (28,990 points)

    A 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:

    Screen Shot 2013-02-08 at 2.16.26 pm.png

    That expression, should you wish to copy it, is:

     

    =INDEX(Breakers, MATCH(A, Breakers :: A, -1))


    Regards,

     

    Jerry

  • 4. Re: Looking for help with If/Then
    Wayne Contello Level 6 Level 6 (13,615 points)

    I think this does NOT match the behavior of the documentation:

    Screen Shot 2013-02-08 at 2.28.46 PM.png

  • 5. Re: Looking for help with If/Then
    Elissa C Level 1 Level 1 (0 points)

    Looking 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 Level 7 Level 7 (28,990 points)

    I doubt that for this purpose there will be fractional amperes entered, so the following would also likely do the job:

    Screen Shot 2013-02-08 at 6.29.51 pm.png

    I just bumped the current values in the header of the lookup table.

     

    Jerry

  • 7. Re: Looking for help with If/Then
    Badunit Level 6 Level 6 (10,815 points)

    Wayne Contello wrote:

     

    I think this does NOT match the behavior of the documentation:

    Screen Shot 2013-02-08 at 2.28.46 PM.png

     

    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 Level 6 Level 6 (13,615 points)

    If 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 top-row value that is less than the search value is 17... the 26 in the look up table is the top-row value

     

    top-row value < search_val   ->  17 < 26 is true

    top-row value < search_val   ->  26 < 26 is NOT true

  • 9. Re: Looking for help with If/Then
    Badunit Level 6 Level 6 (10,815 points)

    But 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 Level 6 Level 6 (13,615 points)

    I missed that part.  Thank you.