Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

If formula to return 0 when same and 1 when different

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.
0
route217
Asked:
route217
  • 3
  • 2
  • 2
2 Solutions
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this...

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

Open in new window

0
 
ProfessorJimJamCommented:
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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Another way....
=IF(SUMPRODUCT(COUNTIF(BA19:BE19,BA19:BE19))=5,1,0)

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
route217Author 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..
0
 
ProfessorJimJamCommented:
please see attached.  the result does show 1
Book1.xlsx
0
 
route217Author Commented:
Apologies projimjam..

End result should be 2 hence two date differences. My fault.. sorry.
0
 
ProfessorJimJamCommented:
please see attached.
Book1.xlsx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now