Solved

Help writing a formula (nested If statements)

Posted on 2014-12-22
18
87 Views
Last Modified: 2014-12-22
Please review attached file that explains the need.  Thanks.
IfStatementsFormula.xlsx
0
Comment
Question by:RWayneH
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40513294
How do you get 1 for B, D and E based on your description?

Based on your results, it seems you just want:
=IF(D3<>D4,1,0)
but your description is:
=IF(AND(D3<>D4,D4=D5),1,0)
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40513333
I agree with Rory, based on your notes in column A the results in column E should be:

1     D4 has nothing above so it doesn't equal but is the same D5
0     D5 is the same as D4 and the same as D6
0     D6 does match D5 but does not match D7
0     D7 Doesn't match D6 or D8
1     D8 does not match D7 but does match D8
0     D9 matches D8 but not D10
0     D10 doesn't match D9 or D11
0     D11 doesn't match D10 and nothing below
0
 

Author Comment

by:RWayneH
ID: 40513335
Sorry I forgot to add the rule:  If the cell above me and the cell below me do not match = 1

I can write the if statements independent of each other, where I am having the issue is putting them all into one formula, so it can copy it down.  Thanks. -R-
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40513350
The description is incomplete.
need to add a line:
in other case: 1

without simplification it is:
=IF(AND(D4<>D3;D4=D5);1;IF(AND(D4=D3;D4=D5);0;IF(AND(D4=D3;D4<>D5);0;1)))
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40513351
It appears that you're trying to identify the first item in each sorted set of items in column D, correct?

If this is true, then insert this formula in cell E4 and copy down:
=IF(COUNTIF($D$4:D4,D4)=1,1,0)

Regards,
-Glenn
0
 

Author Comment

by:RWayneH
ID: 40513352
I updated the attachment.. with the added rule.
IfStatementsFormula2.xlsx
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 40513353
That still wouldn't match your results as it would give a 1 for E6 (A above is not B below).
I still think you just want:
=IF(D3<>D4,1,0)
which produces the results you showed.
0
 

Author Comment

by:RWayneH
ID: 40513377
I think they both work...  E6 is getting a zero when testing...  I guess another way of explaining this is that each unique value needs a "1".  If it shows again it gets a "0".  Does anyone see an issue with either way?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40513383
RWayneH....did you try my formula?  It matches your output - and I believe your intent. :-)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:RWayneH
ID: 40513406
Yes, yours and Rory's are working.  Gauthier, did have issues when the one above and below did not match.  I was working with that one and did not see your posts...  Thanks
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40513414
Based on the requirements I think Rory's makes the most sense.  The only time you want a "1" is when below matches regardless of what above is.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40513429
So long as the data in column D is sorted, Rory's formula is the simplest and most-straightforward.

If the data in column D is not sorted and you still wanted to identify the first occurrence of a value in that column, the COUNTIF formula will do that.

-Glenn
0
 

Author Comment

by:RWayneH
ID: 40513430
oh I was reading it: If above me is not the same as me = 1  if it is the same = 0.  I think we are saying the same thing... I like Glen's however I am having issue decoding that syntax.  =IF(COUNTIF($D$4:D4,D4)=1,1,0)  If an thinking that the ending of 1,0) is like an offset of the cell, one down?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40513432
By my reckoning my formula requires the least processing power/time. :)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40513441
RWayneH:
The formula I provided is broken down like so:

=IF
    (COUNTIF($D$4:D4,D4)=1 <--- If the range from D4 up to the current cell (which happens to also be D4 in the very first occurrence of this formula in cell E4) is equal to 1, then it means that this is the first time this value has occurred in the entire range up to this point
   ,1  <-  if the above is true, then return 1
   ,0)  <-  otherwise (if the count is greater than one), return zero

The formula in cell E9, for example looks like this:
=IF(COUNTIF($D$4:D9,D9)=1,1,0)

Since the COUNTIF function returns 2 here (because there are 2 "C" values at this point, the formula returns a zero.

But, as I noted, so long as your data in column D is sorted, Rory's formula is simpler (and faster).
0
 

Author Closing Comment

by:RWayneH
ID: 40513444
Thanks for the help.
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40513476
My formula match the output or your sheet exactly, you probably made an error when testing it.
But as I said, it can be simplified:
if you match TRUE==1 & FALSE == 0
IF(A;0;1) === not A
IF(A;1;0) === A
IF(A;1;B) === A or B
IF(A;0;B) === not A or B
0
 

Author Comment

by:RWayneH
ID: 40513518
I believe you are right, my testing was not right, sorry for awarding the points to the others.  You deserved some too. -R-
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidate xl 2010 worksheets with text 2 24
File not loading into PowerPivot 4 9
Access Excel export not behaving 2 27
Excel 2016 formulas 5 31
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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now