10 Replies Latest reply: Feb 8, 2013 4:37 PM by Wayne Contello
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
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:

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

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

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

• ###### 5. Re: Looking for help with If/Then
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
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:

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

Jerry

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

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

I missed that part.  Thank you.