Basic IF/OR/AND formula syntax, to analyse a number value series and return Yes or No or NoChange as to whether the value is trending up or down..?

[MickFish]

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:

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

<colgroup><col width="64" style="width: 48pt;" span="29"> <tbody> </tbody>

Simplified data:

score	score	score	score	score	score	score	score	score	score	score
5	5	5		4	3	3	4	4	3	3

<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

 

[MickFish]

G'day Oldbrewer
I managed to get your formula to work for data formatted similar to your earlier table, with some erroneous results (in Red). Q: What does the '/3' mean in the syntax of your formula?

OFFSET:
=IF(K4<(OFFSET($A$1,3,MATCH(LARGE($A$1:$K$1,2),$A$1:$K$1,0))+OFFSET($A$1,3,MATCH(LARGE($A$1:$K$1,3),$A$1:$K$1,0))+OFFSET($A$1,3,MATCH(LARGE($A$1:$K$1,4),$A$1:$K$1,0)))/3,"down","up")

I got similar results using FORECAST. The Up/Down column has IF statement =IF(K4<M4, "Down","Up")..
FORECAST:
=FORECAST(K4,$A$4:$K$4,$A$1:$K$1)

But I could not get TREND to work.
TREND:
{=TREND($A$1:$K$1,$A$4:$K$4,K4)}

Any comments are appreciated, thanks.

1	2	3	4	5	6	7	8	9	10	11	Known Ys
1	2	3	4	5	6	7	8	9	10	11	Known Xs
score	score	score	score	score	score	score	score	score	score	score	Up/Down	FORECAST	TREND	OFFSET
1	5	5		4	3	3	4	4	3	3	Down	3.530303	#VALUE!	down
4	4	3	4	2	4	2				5	Up	3.511364	#VALUE!	up
2	2	2		3	3		2	4	5	2	Down	3.539773	#VALUE!	down
5	2		3	4	3	3				3	Down	3.530303	#VALUE!	down
		3		3					5		Down	3.558712	#VALUE!	down
	3	3	3			3	3	3			Down	3.558712	#VALUE!	down
		4		5	5	5	4	3	2	2	Down	3.539773	#VALUE!	down
		2		3	3	3	2	1	1	4	Up	3.520833	#VALUE!	up
	3	2		3	3	3	2	1	4	2	Down	3.539773	#VALUE!	down
	5	5	5	5	5					2	Down	3.539773	#VALUE!	down
3	3	3	2	2	3	3	3		4	4	Up	3.520833	#VALUE!	up

<colgroup><col width="64" style="width: 48pt;" span="11"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

 

[MickFish]

Hi Oldbrewer
Just wanted to add, that the examples of using these Functions use data set out in Columns, whereas my data is set out in Rows.. Maybe my attempt to flip the syntax to suit is why I am having difficulty? MickFish

 

[oldbrewer]

remember to get the "trend" I compare a result with the average of the previous 3 results hence /3

the principle is the same for data in columns but you need a blank column between each pair of columns to put the up or down or level for each value

blanks in the data are a bit of a problem