Multiple row value-based if/end issue

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
109
Office Version
  1. 2016
Platform
  1. Windows
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"]
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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I don't know how I'd go about giving it a margin of -1 to +1.
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top