Solved

If formula to return 0 when same and 1 when different

Posted on 2016-09-28
7
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

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 31

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

710 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