CODEWITHSUNDEEP
excelrow
Wizhi
Wizhi

Reputation: 6549

Check if row number increases, Excel

I'm looking for a way or ideas (preferable array solution), where you can determine if the numbers in a row increases.

The excel formula should give me "True" for the following sequence, since I want it to exclude #N/A values.

Example: 

0,22    0,275   0,3162  0,36    #N/A    0,46    0,52

Notice: I saw an reddit post "Formula to detect if row values are increasing?" with a similar question. I liked the idea and I have tried to use it with my numbers, but don't get the formula to work/understand it fully.

Upvotes: 0

Views: 859

Answers (1)

NiH
NiH

Reputation: 466

I am not sure if it can be done in one array formula, because the possibility to have N/A values causes some additional complexity. I can suggest a solution with a helper column though. Say your list of values is in A1:A7, then you can get the sign of the difference between the value in A2 and the value in A1 as follows:

= IF( ISNUMBER( A2 ), SIGN( A2 - LOOKUP( 2, 1 /  ISNUMBER( A$1:A1 ), A$1:A1 ) ), 0 )

if you put this formula in B2, you can drag this down to B7. Now if you compare the sum of B2:B7 with the number of increases you expect, you have your answer:

= SUM( B2:B7 ) = COUNT( A1:A7 ) - 1

Refer to this very helpful page to get an explanation of how to get the last non-blank (c.q. numeric) value in a range.

Upvotes: 2

Related Questions