We help IT Professionals succeed at work.

Help writing a formula (nested If statements)

RWayneH
RWayneH asked
on
123 Views
Last Modified: 2014-12-22
Please review attached file that explains the need.  Thanks.
IfStatementsFormula.xlsx
Comment
Watch Question

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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)
Steven CarnahanAssistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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-

Commented:
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)))
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I updated the attachment.. with the added rule.
IfStatementsFormula2.xlsx
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
RWayneH....did you try my formula?  It matches your output - and I believe your intent. :-)

Author

Commented:
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
Steven CarnahanAssistant Vice President\Network Manager
CERTIFIED EXPERT

Commented:
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.
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
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?
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
By my reckoning my formula requires the least processing power/time. :)
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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).

Author

Commented:
Thanks for the help.

Commented:
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

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.