Check if the values of a list are in progressive order.

**Askalian** · 01-18-2013, 09:33 PM

Hello,

I have several series of 13 figures (from 0 to 20), such as :

0 0 0 14 18 3 3 0 0 0 0 0 1

I would like to filter those series with the following condition :

Each figure (except the zeros) should be higher or equal to the first figure (found on the right) which is not a zero.
In the example :

14 should be higher or equal to 18 : FALSE
8 should be higher or equal to 3 : TRUE
3 should be higher or equal to 3 : TRUE
3 should be higher or equal to 1 : TRUE

So, the serie is FALSE, due to the fact that 14 is not higher than 18.

Thanks in advance for your kind help.
Best.

**Special-K** · 01-18-2013, 10:28 PM

Where do you get 8 from? The figure is 18

**Askalian** · 01-18-2013, 11:06 PM

Woops. Sorry.

You are right, it is 18 instead of 8.

**:) Sixthsense :)** · 01-18-2013, 11:46 PM

Can you please show it in excel with expected output for better understanding of your data structure.

**huuthang_bd** · 01-19-2013, 12:38 AM

Hi Askalian,

I use an array formula. Please see attachment.

Regards
huuthang

**Askalian** · 01-19-2013, 07:53 AM

Originally Posted by huuthang_bd

Hi Askalian,

I use an array formula. Please see attachment.

Regards
huuthang

Hi, Huuthang.

Thanks for the help. I have tried this example and it seems not to work :

0 15 2 0 13 7 0 1 0 0 0 1 1 is given as TRUE and it is FALSE

Maybe it will be more simpler if the figures are in cells like in the attached file.
Thanks so much.

Askalian.

**Askalian** · 01-19-2013, 07:56 AM

Originally Posted by :) Sixthsense :)

Can you please show it in excel with expected output for better understanding of your data structure.

Hi, Sixthsense.

Of course. I should have done it, you are right.
Attached is the example file.

Thanks in advance.
Askalian

**Marcol** · 01-19-2013, 09:46 AM

See if this workbook works for you.

Test1 and Test2 are names, see the names manager.

B2 is an array formula =, Confirm with Ctrl+Shift+Enter not just Enter, then drag Down.

Formula:

Please Login or Register  to view this content.

Column D is not required, I have included it only to show what the formula is actually analysing.

[EDIT]
I should have noticed the attachment in Post #6 before I posted this!!!!

**WHER** · 01-19-2013, 12:56 PM

If a UDF is acceptable, test the following:

**Marcol** · 01-24-2013, 01:15 PM

Originally Posted by Askalian

Maybe it will be more simpler if the figures are in cells like in the attached file.
Thanks so much.

Askalian.

Try this Array formula in O3, confirm with Ctrl+Shift+Enter not just Enter, then drag down

Formula:

Please Login or Register  to view this content.

(Thanks to pgc01 - MrExcel MVP @ Mr Excel for the pointer)

Now you have a solution either way ...

See also post#8

**daddylonglegs** · 01-24-2013, 08:17 PM

I used a similar version but with COUNTIF rather than SUBTOTAL

=SUMPRODUCT(B3:M3*(COUNTIF(OFFSET(N3,0,0,1,COLUMN(C3:N3)-COLUMN(C3)-COLUMNS(C3:N3)),">"&B3:M3)>0))=0

Doesn't need CSE

**Marcol** · 01-24-2013, 08:56 PM

Originally Posted by daddylonglegs

I used a similar version but with COUNTIF rather than SUBTOTAL

=SUMPRODUCT(B3:M3*(COUNTIF(OFFSET(N3,0,0,1,COLUMN(C3:N3)-COLUMN(C3)-COLUMNS(C3:N3)),">"&B3:M3)>0))=0

Doesn't need CSE

As is always DLL, you trump me, I'll take this onboard.

Maybe there is a non-volatile solution somewhere?

P.S. Thanks for the earlier compliment and +rep, I try as best as I can.

Check if the values of a list are in progressive order.

LinkBack

Thread Tools

Rate This Thread

Display

Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Re: Check if the values of a list are in progressive order.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions