Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Nested If formula?

Posted on 2013-12-17
Medium Priority
164 Views
One of my colleagues needs to flag each record that has 3 or more consecutive days with a 1 in a column.  Attached simple spreadsheet demonstrates what he's trying to get.
Tricky.xlsx
0
Question by:kerikeri
[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

LVL 22

Accepted Solution

Flyster earned 1200 total points
ID: 39725628
In the first 2 rows, C2 and C3, use this formula:

=IF(SUM(C2:C4)>2,"OK","")

In C4, use this:

=IF(OR(SUM(B2:B4)>2,SUM(B4:B6)>2,SUM(B3:B5)>2),"OK","")

Copy this formula the rest of the way down.

Flyster
Tricky.xlsx
0

LVL 50

Assisted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 39725639
Hello,

are the values always 1 or can there be other numbers? This one, starting in C2 and copied down will cater for any value greater than 0:

=IF(OR(AND(D1="OK",B2>0),AND(B2>0,B3>0,B4>0)),"OK","")

cheers, teylyn
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 400 total points
ID: 39725699
Taking teylyn's formula a step further, this formula will find any non-zero number repeated consecutively three or more.

=IF(OR(AND(D1="OK",B2>0),AND(B2>0,B3=B2,B4=B2)),"OK","")
0

Author Closing Comment

ID: 39725703
Hey thanks, each of your suggestions was helpful in the final solution.  My colleague is impressed!  Thanks for your quick response, much appreciated.
0

## Featured Post

Question has a verified solution.

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

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;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016