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.
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.
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!
If the search_val = 26 and the lookup table has rows like:
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