Multiple logical formula

[Guest]

i wud like to put this formula in excel,if someone can please guide me,

if c1>a1 and if c1>b1 then d1=0,
if c1>a1 and if c1<b1 then d1=c1-b1
if c1<a1 and if c1<b1 then d1=a1-b1
and
if c1<a1 and c1>b1 then d1=a1-c1

 

[Guest]

Try this: in D1

=IF(AND(C1>A1,C1>B1),0,IF(AND(C1>A1,C1<B1),C1-B1,IF(AND(C1<A1,C1<B1),A1-B1,IF(AND(C1<A1,C1>B1),A1-C1,"Unspecified"))))

Will that do?

Mike

 

[Guest]

Try this:

=IF(AND(C1>A1,C1>B1),D1=0,IF(AND(C1>A1,C1<B1),D1=C1-B1,IF(AND(C1<A1,C1<B1),D1=A1-B1,IF(AND(C1<A1,C1>B1),D1=A1-C1))))

 

[David Biddulph]

=IF(C1>A1,IF(C1>B1,0,C1-B1),IF(C1<B1,A1-B1,A1-C1))
or =IF(C1>A1,MIN(C1-B1,0),A1-MAX(B1,C1))

Note that you didn't specify what happens if C1=A1, or if C1=B1.

 

[Guest]

Hi,
thanks a lot for ur help.

--
rgds

Try this:

=IF(AND(C1>A1,C1>B1),D1=0,IF(AND(C1>A1,C1<B1),D1=C1-B1,IF(AND(C1<A1,C1<B1),D1=A1-B1,IF(AND(C1<A1,C1>B1),D1=A1-C1))))

 

[Guest]

Great help Mike, thanks a million.
--
rgds

Try this: in D1

=IF(AND(C1>A1,C1>B1),0,IF(AND(C1>A1,C1<B1),C1-B1,IF(AND(C1<A1,C1<B1),A1-B1,IF(AND(C1<A1,C1>B1),A1-C1,"Unspecified"))))

Will that do?

Mike

 

[Guest]

Your welcome and thaks for the feedback. Just one point you need to consider
what you want to happen if c1 = a1& b1 because you don't specify this. In my
formula this condition returns 'Unspecified"

Mike

 

[Guest]

hi david,

what will be ans in that case if C1=A1, or if C1=B1.

 

[Tim C]

One way:

=MAX(A1,C1)-MAX(B1,C1)

Tim C