Dear reader,
I'm having an issue with a formula. I'm trying to accomplish the following:
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]Stop[/TD]
[/TR]
</tbody>[/TABLE]
So I have 2 columns with values. The first column can be looked at as a category column. The second column has the results of each participant in their category. In the third column I'm trying to show if their are differences in the values between the participants. However, there are a few rules to keep in mind.
Rule 1: The value in column B of each participant in the same category (A) has to be the same, otherwise stop.
Rule 2: If a participant is in a higher category (Column A: 1,2>1) then the results in column B may not be higher (10.5<11), otherwise stop. For example in the column above; eventhough the participants in category 0,8 have the same result, their result is less than the participants in 0,7 so stop.
Optional Rule 3: This is for a fourth column, in here I'm allowing the results of column B to vary by +1 or -1.
In another thread, with some help, I've come to this formula:
=IF(OR(IFERROR(INDEX(AU$2:AU$100,MATCH(AT2,AT$2:AT$100,0)-1)*ISNUMBER(AU1),0)>INDEX(AU$2:AU$100,MATCH(AT2,AT$2:AT$100,0)),LOOKUP(2,1/(AT$2:AT$100=AT2),AU$2:AU$100)>IF(LOOKUP(2,1/(AT$2:AT$100=AT2),AU$3:AU$101)="",10^6,LOOKUP(2,1/(AT$2:AT$100=AT2),AU$3:AU$101))),"STOP","Ok")
However when I'm changing values it appears not to be fulling working.
I'm hoping anyone can point me in the right direction.
Thanks,
Steve
I'm having an issue with a formula. I'm trying to accomplish the following:
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD][TD="bgcolor: #DCE6F1"]
B
[/TD][TD="bgcolor: #DCE6F1"]
C
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD][TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD][TD]
0,7
[/TD][TD]
10,1
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD][TD]
0,7
[/TD][TD]
10,1
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD][TD]
0,8
[/TD][TD]
9
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD][TD]
0,8
[/TD][TD]
9
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD][TD]
1
[/TD][TD]
10,5
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD][TD]
1
[/TD][TD]
10,5
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD][TD]
1
[/TD][TD]
10,5
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD][TD]
1,2
[/TD][TD]
11
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD][TD]
1,2
[/TD][TD]
11
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11
[/TD][TD]
1,3
[/TD][TD]
12
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12
[/TD][TD]
1,3
[/TD][TD]
12
[/TD][TD]Ok[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13
[/TD][TD]
1,5
[/TD][TD]
12
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14
[/TD][TD]
1,5
[/TD][TD]
17
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15
[/TD][TD]
1,6
[/TD][TD]
15
[/TD][TD]Stop[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16
[/TD][TD]
1,6
[/TD][TD]
15
[/TD][TD]Stop[/TD]
[/TR]
</tbody>[/TABLE]
So I have 2 columns with values. The first column can be looked at as a category column. The second column has the results of each participant in their category. In the third column I'm trying to show if their are differences in the values between the participants. However, there are a few rules to keep in mind.
Rule 1: The value in column B of each participant in the same category (A) has to be the same, otherwise stop.
Rule 2: If a participant is in a higher category (Column A: 1,2>1) then the results in column B may not be higher (10.5<11), otherwise stop. For example in the column above; eventhough the participants in category 0,8 have the same result, their result is less than the participants in 0,7 so stop.
Optional Rule 3: This is for a fourth column, in here I'm allowing the results of column B to vary by +1 or -1.
In another thread, with some help, I've come to this formula:
=IF(OR(IFERROR(INDEX(AU$2:AU$100,MATCH(AT2,AT$2:AT$100,0)-1)*ISNUMBER(AU1),0)>INDEX(AU$2:AU$100,MATCH(AT2,AT$2:AT$100,0)),LOOKUP(2,1/(AT$2:AT$100=AT2),AU$2:AU$100)>IF(LOOKUP(2,1/(AT$2:AT$100=AT2),AU$3:AU$101)="",10^6,LOOKUP(2,1/(AT$2:AT$100=AT2),AU$3:AU$101))),"STOP","Ok")
However when I'm changing values it appears not to be fulling working.
I'm hoping anyone can point me in the right direction.
Thanks,
Steve
Last edited: