Conditional formula to return true/false, based on the values of a group of cells

[FlynnXYZ]

I need to check true/false based on the values of 5 cells, I cannot have the numbers in the array be followed by the next more than 2 times.

Example:

1,2,5,7,9 true

1,2,3,5,8 false

10,11,12,13,18 false

Any thoughts on how to achieve this?

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this array formula
=(MAX(FREQUENCY(IF(B1:E1=A1:D1+1,COLUMN(B1:E1)),IF(B1:E1<>A1:D1+1,COLUMN(B1:E1))))+1<3)
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[mjmcder]

Welcome to Mr Excel

Maybe this array formula
=(MAX(FREQUENCY(IF(B1:E1=A1:D1+1,COLUMN(B1:E1)),IF(B1:E1<>A1:D1+1,COLUMN(B1:E1))))+1<3)
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Marcelo,
What does Control + Shift + Enter do, as opposed to just hitting Enter?

Thanks,

 

[Marcelo Branco]

If you're not familiar with array formulas take a look at
http://www.cpearson.com/excel/arrayformulas.aspx

M.

 

[Scott Huish]

If the numbers are all in sequential order, perhaps:
=SUMPRODUCT(--(A1:D1=B1:E1-1))<2

 

[Marcelo Branco]

If the numbers are all in sequential order, perhaps:
=SUMPRODUCT(--(A1:D1=B1:E1-1))<2

Hi Scott

This was the first formula i tried. But i wasn't sure about the expected result in this case

[TABLE="class: grid"]
<tbody>[TR]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

14​

[/TD]
[TD]

15​

[/TD]
[TD]

18​

[/TD]
[/TR]
</tbody>[/TABLE]

Let's wait what the OP says...

M.

 

[FlynnXYZ]

Works like a charm, thank you so much

 

[Marcelo Branco]

Works like a charm, thank you so much

Have you seen my last post? What is the expected result for

[TABLE="class: grid"]
<tbody>[TR]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

14​

[/TD]
[TD]

15​

[/TD]
[TD]

18​

[/TD]
[/TR]
</tbody>[/TABLE]

True or False?

M.

 

[FlynnXYZ]

True like it should, I tried similar combinations right after I saw your post.
Big thank you from Portugal

 

[Marcelo Branco]

True like it should, I tried similar combinations right after I saw your post.
Big thank you from Portugal

You are very welcome!

If the expected result is True in the mentioned case you need the array formula i posted above. I've tried to create a regular formula (non-array) to no avail .
Maybe someone else...

Saudações

M.