If formula to return 0 when same and 1 when different

route217
route217 used Ask the Experts™
on
Hi Experts using excel 2010

I havethe following if formula...

IF(BB19=BA19,0,IF(BC19=BB19,0,IF(BD19=BC19,0,IF(BE19=BD19,0,IF(BF19=BE19,0,1)))))
 
Which should when the previous date is the same as current date return 0 and when different return 1...cannot get it to work. Keeps on returning 0 all the time.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Maybe this...

=IF(OR(BB19=BA19,BC19=BB19,BD19=BC19,BE19=BD19,BF19=BE19),0,1)

Open in new window

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
can u upload a sample file.  the reason you get zero is that in all nested IFS the condition for true is 0 so that would mean that any of these BB19=BA19 BC19=BB19 BD19=BC19 BF19=BE19 are returning true meaning that any of these equal statement are true and therefore it will not reach to the end.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Another way....
=IF(SUMPRODUCT(COUNTIF(BA19:BE19,BA19:BE19))=5,1,0)

Open in new window

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

route217Junior

Author

Commented:
Firstly thanks for the feed back still get a zero.

So.assume row 1
Date 1        Date 2        Date 3       Date 4
Rows 2
01/02/16   02/02/16    02/02/16   03/02/16
Hence end result should be 2 date 1 and 2 are different and 3 and 4..
Microsoft Excel Expert
Top Expert 2014
Commented:
please see attached.  the result does show 1
Book1.xlsx
route217Junior

Author

Commented:
Apologies projimjam..

End result should be 2 hence two date differences. My fault.. sorry.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
please see attached.
Book1.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial