Solved

If formula to return 0 when same and 1 when different

Posted on 2016-09-28
7
55 Views
Last Modified: 2016-09-28
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
Comment
Question by:route217
  • 3
  • 2
  • 2
7 Comments
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41819526
Maybe this...

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

Open in new window

0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41819530
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
 
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41819531
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!

 

Author Comment

by:route217
ID: 41819549
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
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 250 total points
ID: 41819574
please see attached.  the result does show 1
Book1.xlsx
0
 

Author Comment

by:route217
ID: 41819578
Apologies projimjam..

End result should be 2 hence two date differences. My fault.. sorry.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41819761
please see attached.
Book1.xlsx
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question