How do I combine the 4 IF formulas into 1 formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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")*N1

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))
 
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
 
Hi


Robert said:
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.
Click to expand...

It is a single formula! Composed of several nested functions (i.e. a
function inside function, up to 3 nesting levels).
 
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
 
Back
Top