Formula

[John Savy]

Could you write a formula to function as:
"A1 + B1 = 120"
- assuming B1 consists of numbers from 1 to 20, that means the formula
should calculate the value of A1 plus B1 (1, 2, ..20).
If the the summation becomes equal 120, then the sub-number of B1 (which is
any number between 1 to 20) otherwise blank.

 

[Sheeloo]

Do you want this in code or as a formula?
Try
=IF(A1+B1<=120,"",B1)
or
=IF(A1+B1<=120,A1+B1,B1)
or
=IF(A1+B1<=120,A1+B1,IF(B1<=20,B1,""))

 

[John Savy]

Thanks for your respond,

the cell B1 is a known number, and that is 20. consists of from b1 to b20.
Therefore, the formula should " A1 + (any one of the b1..b20) to be equal to
120.
I'm looking for any combination of A1 and b1..b20 in order to get total of
120.

 

[Sheeloo]

Do you want to know which cell in B1:B20, when added to A1 gives you 120?
[This will happen only if A1 is between 100-119.]

If the number is in A1 then
="B"&(120-A1)
will give you the cell...

You can also use
=IF(AND(A1>=100,A1<=119),"B"&(120-A1),"")
if the number in A1 can be outside the range 100-119