Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
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. -- John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
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" wrote: 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. -- John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
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. -- John "Sheeloo" wrote: 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" wrote: 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. -- John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
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 "John Savy" wrote: 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. -- John "Sheeloo" wrote: 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" wrote: 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. -- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |