Addtional condition in array formula

[sachin483]

i have posted in other forum but no response

I have this grid for commission payout arrived at this formula,but if the ach% is >120 it should pay 120% amount now reflecting zero can we modify this formula ie:- grid can be of >120 to 150 but after formula checks at end if no amount is there of that ach% it should pay the last max amount and so on

DIV ACH A
ZZ 100 16876
ZZ 101 17976
ZZ 102 19076
ZZ 103 20176
ZZ 104 21276
ZZ 105 22376
ZZ 106 25996
ZZ 107 27516
ZZ 108 29036
ZZ 109 30556
ZZ 110 32076
ZZ 111 34861
ZZ 112 36496
ZZ 113 38131
ZZ 114 39766
ZZ 115 41401
ZZ 116 44716
ZZ 117 46456
ZZ 118 48196
ZZ 119 49936
ZZ 120 51676


DD WW ACH PAY AA2 I2
ZZ A 106 25996 85 95
ZZ A 120 51676 85 95
ZZ A 125 0 85 95

[sachin483]

while posting other forum link this error reflecting

Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.

[sachin483]

http://www.ozgrid.com/forum/showthread.php?t=200562
http://www.mrexcel.com/forum/excel-q...on-payout.html
http://www.excelforum.com/excel-general/1148411-addtional-condition-in-array-formula.html

[p45cal]

in G2:
=IFERROR(INDEX('GRID (2)'!$C$3:$F$23,MATCH(1,--('GRID (2)'!$A$3:$A$23=A2)*--('GRID (2)'!$B$3:$B$33=MIN(E2,MAX('GRID (2)'!$B$3:$B$23)))*--(H2>=85)*--(F2>=95),0),MATCH(D2,'GRID (2)'!$C$2:$F$2,0)),0)
CSE entered of course.
Copy down.
There will still be some zeroes because variously the values in columns E, F & H are out of range:
G2 is 0 because column E is below the minimum in column B of GRID(2)
G8 is 0 because the value in E is less than 95 and also because column E is below the minimum in column B of GRID(2)
G9 is 0 because the value in F is less than 95.

ps. post posting: I notice in the formula there are some uneven ranges:
=IFERROR(INDEX('GRID (2)'!$C$3:$F$23,MATCH(1,--('GRID (2)'!$A$3:$A$23=A2)*--('GRID (2)'!$B$3:$B$33=MIN(E2,MAX('GRID (2)'!$B$3:$B$23)))*--(H2>=85)*--(F2>=95),0),MATCH(D2,'GRID (2)'!$C$2:$F$2,0)),0)
I suspect these should be of similar size.

[sachin483]

Thanks a lot p45cal and you have noticed uneven ranges which you have highlighted means there is some formula error or some doubt

[Aussiebear]

Thanks a lot p45cal and you have noticed uneven ranges which you have highlighted means there is some formula error or some doubt

Sorry but I fail to understand your attitude here. P45cal has indicated some serious errors in your code its sort of blaise from you..., you could try to be a little more careful.

[sachin483]

same was my mistake while editing the formula range, thanks, i will be more careful onwards