I wish to analyse a number series and determine if the latest number is higher or lower when compared to previous number values..?
=IF(AK2<>"",IF(AK2=5,"NO",IF(OR(AND(AK2<>"",AK2<AH2),AND(AE2<>"",AK2<AE2),AND(AB2<>"",AK2<AB2),AND(Y2<>"",AK2<Y2),AND(V2<>"",AK2<V2),AND(T2<>"",AK2<T2),AND(Q2<>"",AK2<Q2),AND(N2<>"",AK2<N2),AND(L2<>"",AK2<L2),AND(J2<>"",AK2<J2),AND(G2<>"",AK2<G2),AND(E2<>"",AK2<E2)),"YES" then etc etc for the No and NoChange
Example data:
[TABLE="width: 1392"]
<colgroup><col width="64" style="width: 48pt;" span="29"> <tbody>[TR]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.4 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.1 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.3 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.4 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q1 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.8 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Simplified data:
[TABLE="width: 528"]
<colgroup><col width="64" style="width: 48pt;" span="11"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Sometimes there is no value, and the formula needs to ignore these Null values..
Thanks kindly for your time..
MickFish
ddd
=IF(AK2<>"",IF(AK2=5,"NO",IF(OR(AND(AK2<>"",AK2<AH2),AND(AE2<>"",AK2<AE2),AND(AB2<>"",AK2<AB2),AND(Y2<>"",AK2<Y2),AND(V2<>"",AK2<V2),AND(T2<>"",AK2<T2),AND(Q2<>"",AK2<Q2),AND(N2<>"",AK2<N2),AND(L2<>"",AK2<L2),AND(J2<>"",AK2<J2),AND(G2<>"",AK2<G2),AND(E2<>"",AK2<E2)),"YES" then etc etc for the No and NoChange
Example data:
[TABLE="width: 1392"]
<colgroup><col width="64" style="width: 48pt;" span="29"> <tbody>[TR]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.4 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.1 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.3 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.4 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q1 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.8 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Simplified data:
[TABLE="width: 528"]
<colgroup><col width="64" style="width: 48pt;" span="11"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Sometimes there is no value, and the formula needs to ignore these Null values..
Thanks kindly for your time..
MickFish
ddd