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:
<colgroup><col width="64" style="width: 48pt;" span="29">
<tbody>
</tbody>
Simplified data:
<colgroup><col width="64" style="width: 48pt;" span="11">
<tbody>
</tbody>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:
3.4 Risk | Score | Q2 -15-16 | 3.5 Risk | Score | 3.6 Risk | Score | 3.7 Risk | Score | Q3 -15-16 | 4.1 Risk | Score | Q4 -15-16 | 4.3 Risk | Score | 4.4 Risk | Score | Q1 -16-17 | 4.5 Risk | Score | Q2 -16-17 | 4.6 Risk | Score | Q3 -16-17 | 4.7 Risk | Score | Q4 -16-17 | 4.8 Risk | Score |
Very High | 5 | Very High | 5 | Very High | 5 | High | 4 | Moderate | 3 | Moderate | 3 | High | 4 | High | 4 | Moderate | 3 | Moderate | 3 |
Simplified data:
score | score | score | score | score | score | score | score | score | score | score |
5 | 5 | 5 | 4 | 3 | 3 | 4 | 4 | 3 | 3 |
Thanks kindly for your time..
MickFish
ddd