check value increase continuously

thanks for the help jeffrey.


sorry that I have not given you the requirement in detail.

The data range I have is not consistant
say row 1 will have 0 0 0 1 5 8 18 0 0 0
row 2 will have 0 0 4 9 3 14 19 20 25 0
in such case I want to ignore the zeros either side and validate only between the first and the last value grater than 0 (say row 1 , cell value 1 to cell value 18
row 2 , cell value 4 to cell value 25)

I have already found the cell adresses of the first and last value that are grater than zero which varies from row to row.

I cannot use the simple IF formula you told because of this.

Hope that you understand my requirement.

Originally Posted by jeffreybrown

Hello & Welcome to the board,

If your data is as simple as your example...

Try...

Please Login or Register  to view this content.

I guess you could use and OR setup also...

Please Login or Register  to view this content.

@ TMS
Neither am I ... !

thks TMS, your suggestion solved 50% of my problem.

but if there is a 0 value inbetween it gives a wrong answer.

say
A B C D E F G H I J
1 Jhon 0 0 0 3 4 7 19 0 0 Yes
2 Mary 0 0 4 6 0 9 20 31 0 Yes (should be "No)

basically these are sales figures and 0 start and end means the person has not joined the company. but 0 in the middle means she has not acheived the sales.

this is what I want to find.
Hope little improvement to your formula will help me if could you pls.

Originally Posted by TMShucks

Maybe:

=IF(SUMPRODUCT(--(B1:J1<>0),--(B1:J1<A1:I1))=0,"yes","no")

or:

=IF(SUM(B1:J1)=0,"no",IF(SUMPRODUCT(--(B1:J1<>0),--(B1:J1<=A1:I1))=0,"yes","no"))


Regards

thks Marcol,

sorry to trouble you again. this is the first time I am writing to the help forum and therefore apologies for any inconvenience

I have not change my original conditions infact added a few.

your trick solves the 0 (zero) issue but not the others.

here are the complete set of conditions. If you could pls help me to achive the total solution.

say sales figures are from year 2000 to 2010.(A B C columns are years)

1. there can be any number of zeros at the begining years (person has joined the company in a latter year may be in 2001 or 2002 or 2003 etc.)
2. there can be any number of zeros in ending years (person has left the company before the 2010.ex in 2007 or 2008 or 2009 or even in 2010 etc.)
3. there should be a continuous improvement in sales (need not be the same increment but there cannot be either repitition of values or zeros. )
ex. in 2005 value if the value is 6 in a latter year same amount (6) is not valid.
0 inbetween two figures means person has not achieved any sales which also is not valid

assume data range is column C to L

hope you can help me with the above

Originally Posted by Marcol

Okay that's the third time the rules have changed.

Try this
With your Names in Column A and your data beginning in Column C
In B1

Please Login or Register  to view this content.

This will handle up to 16 numbers per row.

Or a dynamic solution

Please Login or Register  to view this content.

The number of entered numbers per row is only limited by the sheet width