Excel forumla

[mdarsot]

I have spreadsheet like belwo

123 123
124 128
125 127 //problem column above is higher
125 130
130 138

GOOD BAD


what will be the formula to validate if above 5 columns is in assending order. Meaning column below is not higher then column above. They can be equal but column below another cannot be higher. The results of the formula must state word "good" or "bad" depending on validity check.

 

[AnotherHiggins]

Just checking - I don't suppose you could just sort ascending?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mdarsot]

no

 

[Gavona]

=and(b2>b1,b3>b2,b4>b3.....)

Gavin

 

[AnotherHiggins]

An array formula ought to be able to do it. I'm sure there's a more elegant solution using arrays, but I'm in a bit of a hurry to get out of here right now. So this was my first crack at it:
[tt]
123 123
124 128
125 [!]127[/!]
125 130
130 138
[highlight]999[/highlight] [highlight]999[/highlight] <-add this row at the bottom of your data[/tt]

[tab]=SUM((A2:A7>A1:A6)*1) = COUNTA(A1:A6)
entered as an array formula ([Ctrl]+[Shft]+[Enter] instead of just [enter])

Like I said, there's probably a more elegant solution that wouldn't require you to add a row of numbers.

Perhaps one of our array formula gurus will stop by.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

=if(and(b2>b1,b3>b2,b4>b3.....),"Good","Bad")

Also conditional formating could be used to highlight any cell where the value in the row above was greater than the value in the cell.

But when you speak of columns do you mean rows?

Gavin