Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I combine the 4 IF formulas into 1 formula
K1=IF(D1<0,1,0)
L1=IF(F1<0,1,0) M1=IF(H1<0,1,0) N1=IF(SUM(K1:M1)=0,0,1) O1=IF(A1=MTH,N1,0) Assistance required. How can the formula in O1 combine K1,L1,M1,N1. It is a simple formula, but I admit I am not able to get it right at all even after looking through several examples posted in this forum. Thank you. RobertR |
#2
|
|||
|
|||
You can use boolean results (False is '0', True is '1') to simplify your
equation: For example, if you use =D1<0 as a formula Excel will evaluate the result 1 if D1 does not contain 0 (ie TRUE) or 1 if D1 contains anything else (ie FALSE). Use parenthesis and you can do fast, simple (when you get used to the logic) evaluations. In place of the equation you have at N1 =(D1<0)+(F1<0)+(H1<0)0 "Robert" wrote in message ... K1=IF(D1<0,1,0) L1=IF(F1<0,1,0) M1=IF(H1<0,1,0) N1=IF(SUM(K1:M1)=0,0,1) O1=IF(A1=MTH,N1,0) Assistance required. How can the formula in O1 combine K1,L1,M1,N1. It is a simple formula, but I admit I am not able to get it right at all even after looking through several examples posted in this forum. Thank you. RobertR |
#3
|
|||
|
|||
Hi
When you want N1 returned, when ALL conditions are filled at once, then: =(D1<0)*(F1<0)*(H1<0)*(SUM(K1:M1))*(A1="MTH")*N 1 When you want 1 returned, when 1st, 2nd or 3rd condition are filled, 0 when no of first 3 are filled but 4th is, and N1 when only 5th condition is filled, then: =IF(SUM(ABS(D1),ABS(F1),ABS(H1))0,1,IF(SUM(K1:M1) =0,0,(A1="MTH")*N1)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Robert" wrote in message ... K1=IF(D1<0,1,0) L1=IF(F1<0,1,0) M1=IF(H1<0,1,0) N1=IF(SUM(K1:M1)=0,0,1) O1=IF(A1=MTH,N1,0) Assistance required. How can the formula in O1 combine K1,L1,M1,N1. It is a simple formula, but I admit I am not able to get it right at all even after looking through several examples posted in this forum. Thank you. RobertR |
#4
|
|||
|
|||
Steve, taking your cue, have fully achieved my objective i.e. a single formula.
Thank you. Arvi, 1st part, I follow your explanation. 2nd part - seems to be similar to what I already had. It is not a single formula. Will be trying out. Thanks to you too for your effort. RobertR |
#5
|
|||
|
|||
Hi
"Robert" wrote in message ... Steve, taking your cue, have fully achieved my objective i.e. a single formula. Thank you. Arvi, 1st part, I follow your explanation. 2nd part - seems to be similar to what I already had. It is not a single formula. It is a single formula! Composed of several nested functions (i.e. a function inside function, up to 3 nesting levels). -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#6
|
|||
|
|||
Arvi, I misunderstood your formula. I retyped the formula and with help from
"MS suggested formula". Yes it works ( a good single formula). Sorry my mistake. Once again thank you. RobertR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to combine cells using if/then | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Formulas appear in cell instead of formula result | Excel Worksheet Functions | |||
How can I combine IF, COLUMN, and LARGE formulas in a single cell? | Excel Worksheet Functions | |||
Tricky formulas needed | Excel Worksheet Functions |