3 stupid, simple IFs

[Guest]

I'm trying to enter a simple (so I thought) 3-If formula, but keep getting
errors:

2 IFs work:
=IF(B1>C1,B1-C1,IF(D1>C1,D1-C1,""))
This formula works nicely for most of what I need, either the # or nothing.
B C D result
1 1

B C D
1 1

B C D
1

First 2 samples above result in 1, the 3rd results in "", giving me the
result I need.

However, I also need a result of the D cell (2) if B=C=D.
Trying to enter that 3rd IF isn't working. My unworking formula:

=IF(B1>C1,B1-C1,IF(B1=C1=D1,D1),IF(D1>C1,D1-C1),"")

The #'s will vary, but the below example is the result(2) I'd need in this
situation:
B C D
2 2 2 2

Any help for this problem would be greatly appreciated.
Thanks much,

Steve

 

[Guest]

Try:
=IF(B1>C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1>C1,D1-C1),"")

 

[Guest]

Thanks, ahh, the 'and'...
Now I'm getting the #value! error

Here's row 8
=IF(B8>C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8>C8,D8-C8,"")

B C D result
1 1 1 #value!

Thanks,
Steve

 

[Anne Troy]

Just a few things out of place, Steve:
=IF(B8>C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8>C8,D8-C8,"")))
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Guest]

Thanks, it works, finally !
One small problem.
My original formula produced a blank cell, which I wanted, when nothing is
in B, C, or D:
B C D result

The new formula produces a 0
B C D result
0
Why a zero and not a "" ?

Thanks again,

Steve

 

[Anne Troy]

Try this:
=IF(SUM(B88)=0,"",IF(B8>C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8>C8,D8-C8,"")
)))
The zero was as a result of them all equaling each other (if and b8=c8 and
c8=d8, you return d8, which is nothing or zero).
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Guest]

Perfect !!!

Thank you very much,

Steve

Try this:
=IF(SUM(B88)=0,"",IF(B8>C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8>C8,D8-C8,"")
)))
The zero was as a result of them all equaling each other (if and b8=c8 and
c8=d8, you return d8, which is nothing or zero).
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[CLR]

Steve............
Here's a mod to eliminate errors in case you might get TEXT
entrys...........

=IF(COUNTA(B88)>COUNT(B88),"",IF(SUM(B88)=0,"",IF(B8>C8,B8-C8,IF(AND(B
8=C8,B8=D8),D8,IF(D8>C8,D8-C8,"")))))

All one line.......watchout for wordwrap...........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks,

I'll check it out.

Steve

Steve............
Here's a mod to eliminate errors in case you might get TEXT
entrys...........

=IF(COUNTA(B88)>COUNT(B88),"",IF(SUM(B88)=0,"",IF(B8>C8,B8-C8,IF(AND(B
8=C8,B8=D8),D8,IF(D8>C8,D8-C8,"")))))

All one line.......watchout for wordwrap...........

Vaya con Dios,
Chuck, CABGx3