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

 

[Special-K99]

Your formula is invalid, the forum may have corrupted it (places spaces before and after any < > characters).

And what cell is AK2 in your diagram?

 

[oldbrewer]

	1	2	3		4	5	6	7	8	9	10
	score	score	score	score	score	score	score	score	score	score	score
	5	5	5		4	3	3	4	4	3	3
											down
			I am comparing the last score with the three previous scores
	=IF(L3<(OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,2),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,3),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,4),$B$1:$L$1,0)))/3,"down","up")
			note that comparing a single value with a previous value, or the average of some prfevious values is not a statistically valid definition of a trend

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

 

[MickFish]

Thanks Special-K99
Hopefully this is clearer. I very much appreciate your attention.
Clumsily tries to check if a cell is firstly not Null (<>), then check if value K3 is lower ('Yes'), higher ('No') or equal to ('No Change') the preceding values (A:J). It does appear to return the desired result, but I am uncertain of the accuracy (it is dragged down 000's of rows) or if it truly returns an OVERALL up or down 'trend'..??
The formula:
=IF(K3<>"",IF(K3=5,"NO",IF(OR(AND(J3<>"",J3<K3),AND(I3<>"",J3<I3),AND(H3<>"",K3<H3),AND(G3<>"",K3<G3),AND(F3<>"",K3<F3),AND(E3<>"",K3<E3),AND(D3<>"",K3<D3),AND(C3<>"",K3<C3),AND(B3<>"",K3<B3),AND(A3<>"",K3<A3)),"YES",
IF(OR(AND(J3<>"",J3>K3),AND(I3<>"",J3>I3),AND(H3<>"",K3>H3),AND(G3<>"",K3>G3),AND(F3<>"",K3>F3),AND(E3<>"",K3>E3),AND(D3<>"",K3>D3),AND(C3<>"",K3>C3),AND(B3<>"",K3>B3),AND(A3<>"",K3>A3)),"NO",
IF(OR(AND(J3<>"",J3=K3),AND(I3<>"",J3=I3),AND(H3<>"",K3=H3),AND(G3<>"",K3=G3),AND(F3<>"",K3=F3),AND(E3<>"",K3=E3),AND(D3<>"",K3=D3),AND(C3<>"",K3=C3),AND(B3<>"",K3=B3),AND(A3<>"",K3=A3)),"NoChange","NA")))),"NotInCPM")

Simplified data:
A B C D E F G H I J K

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

<tbody>
</tbody>

 

[MickFish]

Thanks very much oldbrewer (I am drinking a tasty German beer called Uberbrau)
I looked up the functions you use and nearly follow the logic. Nearly..
Q: Will this formula copy down over 000's of rows? Or do i need to remove the $?
Q: Does $A$1 refer to your top-left cell, value of 1?
Q: Why does your range $B$1:$L$1 not begin from $A?
Q: In the LARGER function, does it matter if the preceeding cells I wish to reference are not sequentially prior to L3? ie you reference 2,3 & 4 whereas my data are 3, 6, 9,...

Thanks again MickFish

 

[oldbrewer]

copy my formula down as it is
$A$1 is the cell with a 1 in it
it should begin $A$1 well spotted (just checking you were paying attention)
my top row is a helper row in effect numbering the data sequence - do not understand "my data are 3,6,9

 

[MickFish]

Thanks oldbrewer that works, except it throws the #NUM ! error when it encounters a cell with a NULL or blank value.. LARGE(array,k) - if K<=0 #NUM !
Could you please extend your formula back to the Null value cell (D3), showing how you would (possibly) use IF(ISERROR.. to avoid the error?
Thanks again. Where do I send the beer? MickFish

 

[oldbrewer]

notice in my post 3 there is no number in the top row above the blank - so blanks will not affect it. So just in case there is a n/a in the score cell

=if(iserror(l3),"",IF(L3<(OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,2),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,3),$B$1:$L$1,0))+OFFSET($A$1,2 ,MATCH(LARGE($B$1:$L$1,4),$B$1:$L$1,0)))/3,"down","up"))

note extra close bracket on end

are you in UK if so I will fetch the beer.............

 

[MickFish]

Nah I'm in the colonies, but I do keep and eye on the red devils and both eyes on Rangers. They need another season yet, but I hope to come over next year and watch them get up over Celtic, at Ibrox. I'll be pissing in their Bovril and drinking any live pub beer I can get my hands on..
Thanks again. The bother with the ISERROR is that it returns "" whenever an error is encountered (as it is meant to), when what I really want it to do is disregard and Null values encountered and rather assess what valid values there are across the row array and return the up or down value.

My original clunky IF/OR/AND does this, but not very well.. It didn't copy very well below, but it first checks if the cell value is not blank (<>) then does the three options of Yes No and NoChange. Trouble is it does return a few NA as well.

=IF(K3<>"",IF(K3=5,"NO",IF(OR(AND(J3<>"",J3<k3),and(i3<>"",J3<i3),and(h3<>"",K3<h3),and(g3<>"",K3<g3),and(f3<>"",K3<f3),and(e3<>"",K3<e3),and(d3<>"",K3<d3),and(c3<>"",K3<c3),and(b3<>"",K3<b3),and(a3<>"",K3<a3)),"yes",
IF(OR(AND(J3<>"",J3>K3),AND(I3<>"",J3>I3),AND(H3<>"",K3>H3),AND(G3<>"",K3>G3),AND(F3<>"",K3>F3),AND(E3<>"",K3>E3),AND(D3<>"",K3>D3),AND(C3<>"",K3>C3),AND(B3<>"",K3>B3),AND(A3<>"",K3>A3)),"NO",
IF(OR(AND(J3<>"",J3=K3),AND(I3<>"",J3=I3),AND(H3<>"",K3=H3),AND(G3<>"",K3=G3),AND(F3<>"",K3=F3),AND(E3<>"",K3=E3),AND(D3<>"",K3=D3),AND(C3<>"",K3=C3),AND(B3<>"",K3=B3),AND(A3<>"",K3=A3)),"NoChange","NA")))),"NotInCPM")</a3)),"yes",
</b3),and(a3<></c3),and(b3<></d3),and(c3<></e3),and(d3<></f3),and(e3<></g3),and(f3<></h3),and(g3<></i3),and(h3<></k3),and(i3<>

Anyway oldbrewer, you have been a huge help thanks and I have learnt a lot. You now know I cannot get you the beer. Not this year anyways!

 

[oldbrewer]

if there is not a number in the top row then whatever is in row 3 is ignored. Cannot see the issue - can you explain it again. (glad to help a bit)