Formula to check if values in a range are in ascending order (ignoring text & blanks)

[Kelvin Stott]

I have a range, say B4:B13, in which each cell may contain a value, some text, or may be left blank, for example:

[TABLE="width: 50"]
<tbody>[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]""[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]""[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula to check if the numerical values in this range are in ascending order, but ignoring any text and blank cells, so the example above should give a TRUE result as it satisfies this criteria, while the range below should give a FALSE result as it doesn't (note the 9 and 6 are switched in reverse order):

[TABLE="width: 50"]
<tbody>[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]""[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]""[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas, please?

Thanks for any help!

Kelvin

 

[Kelvin Stott]

OK I managed to solve it myself ... with a 2D array formula:

<transpose(b$4:b$13))))))}
<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))[ quote]
<transpose(b$4:b$13))))))"
<transpose(b$4:b$13))))))
=NOT(OR(IF(ROW(B$4:B$13)>TRANSPOSE(ROW(B$4:B$13)),IF(ISNUMBER(B$4:B$13),IF(TRANSPOSE(ISNUMBER(B$4:B$13)),B$4:B$13<TRANSPOSE(B$4:B$13))))))

<transpose(b$4:b$13))))))"
[*] Unfortunately this stupid text editor (in Google Chrome) would not let me include a less than symbol!
<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))

Genius! </transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))"
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))"
</transpose(b$4:b$13))))))[></transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))}

 

[Kelvin Stott]

OK I managed to solve it myself ... with a 2D array formula:

<transpose(b$4:b$13))))))}
=NOT(OR(IF(ROW(B$4:B$13)>TRANSPOSE(ROW(B$4:B$13)),IF(ISNUMBER(B$4:B$13),IF(TRANSPOSE(ISNUMBER(B$4:B$13)),B$4:B$13 ... [<*] ... TRANSPOSE(B$4:B$13))))))

<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))"
[*] unfortunately="" this="" stupid="" text="" editor="" (in="" google="" chrome)="" would="" not="" let="" me="" include="" a="" less="" than="" symbol!
<transpose(b$4:b$13))))))
<transpose(b$4:b$13))))))
Genius!

* Unfortunately this stupid text editor (in Google Chrome) would not let me include a simple less than symbol!
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))"
[*]></transpose(b$4:b$13))))))
</transpose(b$4:b$13))))))}

 

[RoryA]

* Unfortunately this stupid text editor (in Google Chrome) would not let me include a simple less than symbol!

Just put a space after the < symbol, or use the advanced editor and turn off HTML when making the post.

 

[Kelvin Stott]

Thanks, then here is the correct formula (entered with Ctrl + Shift + Enter):

=NOT(OR(IF(ROW(B$4:B$13)>TRANSPOSE(ROW(B$4:B$13)),IF(ISNUMBER(B$4:B$13),IF(TRANSPOSE(ISNUMBER(B$4:B$13)),B$4:B$13< TRANSPOSE(B$4:B$13))))))