Rebates Formula with Twist

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

I have formula in Column AF that works but it's very long winded. Hoping someone has better approach.
Please note I can't change spreadsheet regarding trigger rates and trigger %.


Excel 2013 32 bit

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
3
Vendor CodeVendorTrigger 1T1 %Trigger 2T2 %Trigger 3T3 %Trigger 4T4%Trigger 5T5 %Trigger 6T6 %Trigger 7T7 %Trigger 8T8 %Trigger 9T9 %AmountCalc
4
00000390Morning GYM
$ 500,000​
1.5%​
$ 750,000​
3.0%​
$ 999,000​
4.5%​
$ -​
0.0%​
$ -​
0.0%​
$ 176,526​
$ -​
0.0%​
5
00000743Olympia GYM
$ 1​
0.5%​
$ 80,000​
1.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 42,632​
$ 213​
0.5%​
6
00000441Bean GYM
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 11,888​
$ -​
0.0%​
7
00000380Gallery GYM
$ 1​
0.5%​
$ 2,200,000​
2.0%​
$ 2,700,000​
4.0%​
$ 3,500,000​
5.3%​
$ -​
0.0%​
$ 1,290,429​
$ 6,452​
0.5%​
8
00000876Energy GYM
$ 500,000​
1.0%​
$ 750,000​
2.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 370,509​
$ -​
0.0%​
9
00000713Fiesta GYM
$ 110,000​
1.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 115,328​
$ 1,153​
1.0%​
10
00000692Eagle GYM
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 4,264​
$ -​
0.0%​
11
00000758Gain GYM
$ 1​
0.5%​
$ 150,000​
1.0%​
$ 200,000​
1.5%​
$ 250,000​
2.5%​
$ -​
0.0%​
$ 86,298​
$ 431​
0.5%​
12
00000614Heat GYM
$ 1​
1.0%​
$ 1,820,000​
2.0%​
$ 2,055,555​
3.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 1,434,655​
$ 14,347​
1.0%​
13
00000905Focus GYM
$ 1​
1.0%​
$ 138,342​
2.0%​
$ 142,257​
3.0%​
$ 146,173​
4.0%​
$ 150,088​
5.0%​
$ 156,614​
6.0%​
$ 163,139​
7.0%​
$ 169,665​
8.0%​
$ 176,190​
9.0%​
$ 108,360​
$ 1,084​
1.0%​
14
00000381Compound GYM
$ 1​
2.0%​
$ 365,001​
4.0%​
$ 415,001​
4.5%​
$ 465,000​
5.0%​
$ -​
0.0%​
$ 300,611​
$ 6,012​
2.0%​
15
00000792Circulate GYM
$ 1​
0.5%​
$ 145,001​
1.0%​
$ 200,001​
1.5%​
$ -​
0.0%​
$ -​
0.0%​
$ 157,216​
$ 1,572​
1.0%​
16
00000226GYMverse
$ 100,000​
1.0%​
$ 200,000​
2.0%​
$ 300,000​
3.0%​
$ 400,000​
4.0%​
$ 500,000​
5.0%​
$ 161,796​
$ 1,618​
1.0%​
17
00000126Fine GYM
$ 1​
1.0%​
$ 2,260,000​
2.0%​
$ 2,900,000​
3.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 1,511,282​
$ 15,113​
1.0%​
18
00000018Sunrise GYM
$ 65,000​
2.0%​
$ 80,000​
4.0%​
$ 120,000​
6.0%​
$ 150,000​
8.0%​
$ -​
0.0%​
$ 870,991​
$ 69,679​
8.0%​
19
00000071GYMbea
$ 1​
1.0%​
$ 250,000​
2.0%​
$ 270,000​
3.0%​
$ -​
0.0%​
$ -​
0.0%​
$ 132,689​
$ 1,327​
1.0%​
20
00000377Falcon GYM
$ 250,000​
1.0%​
$ 420,000​
1.5%​
$ 650,000​
2.0%​
$ 850,000​
2.5%​
$ 1,000,000​
3.0%​
$ 807,810​
$ 16,156​
2.0%​

Sheet: Sheet1

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][th]
L
[/th][th]
M
[/th][th]
N
[/th][th]
O
[/th][th]
P
[/th][th]
Q
[/th][th]
R
[/th][th]
S
[/th][th]
T
[/th][th]
U
[/th][th]
V
[/th][th]
W
[/th][th]
X
[/th][th]
Y
[/th][th]
Z
[/th][th]
AA
[/th][th]
AB
[/th][th]
AC
[/th][th]
AD
[/th][th]
AE
[/th][th]
AF
[/th][th]
AG
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Vendor Code[/td][td]Vendor[/td][td][/td][td]Trigger 1[/td][td]T1 %[/td][td][/td][td]Trigger 2[/td][td]T2 %[/td][td][/td][td]Trigger 3[/td][td]T3 %[/td][td][/td][td]Trigger 4[/td][td]T4%[/td][td][/td][td]Trigger 5[/td][td]T5 %[/td][td][/td][td]Trigger 6[/td][td]T6 %[/td][td][/td][td]Trigger 7[/td][td]T7 %[/td][td][/td][td]Trigger 8[/td][td]T8 %[/td][td][/td][td]Trigger 9[/td][td]T9 %[/td][td][/td][td]Amount[/td][td]Calc[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]00000390[/td][td]Morning GYM [/td][td][/td][td]
$ 500,000​
[/td][td]
1.5%​
[/td][td][/td][td]
$ 750,000​
[/td][td]
3.0%​
[/td][td][/td][td]
$ 999,000​
[/td][td]
4.5%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 176,526​
[/td][td]
=$AE4*IF($AE4<$D4,0,IF(OR(AND($D4=1,$G4=0),AND($AE4>=$D4,$AE4<=IF($G4=0,$AE4,$G4))),E4,IF(AND($AE4>=$G4,$AE4<=IF($J4=0,$AE4,$J4)),H4,IF(AND($AE4>=$J4,$AE4<=IF($M4=0,$AE4,$M4)),K4,IF(AND($AE4>=$M4,$AE4<=IF($P4=0,$AE4,$P4)),N4,IF(AND($AE4>=$P4,$AE4<=IF($S4=0,$AE4,$S4)),Q4,IF(AND($AE4>=$S4,$AE4<=IF($V4=0,$AE4,$V4)),T4,IF(AND($AE4>=$V4,$AE4<=IF($Y4=0,$AE4,$Y4)),W4,IF(AND($AE4>=$Y4,$AE4<=IF($AB4=0,$AE4,$AB4)),Z4,IF($AE4>=$AB4,AC4,"error"))))))))))​
[/td][td]
=+AF4/AE4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]00000743[/td][td]Olympia GYM [/td][td][/td][td]
$ 1​
[/td][td]
0.5%​
[/td][td][/td][td]
$ 80,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 42,632​
[/td][td]
=$AE5*IF($AE5<$D5,0,IF(OR(AND($D5=1,$G5=0),AND($AE5>=$D5,$AE5<=IF($G5=0,$AE5,$G5))),E5,IF(AND($AE5>=$G5,$AE5<=IF($J5=0,$AE5,$J5)),H5,IF(AND($AE5>=$J5,$AE5<=IF($M5=0,$AE5,$M5)),K5,IF(AND($AE5>=$M5,$AE5<=IF($P5=0,$AE5,$P5)),N5,IF(AND($AE5>=$P5,$AE5<=IF($S5=0,$AE5,$S5)),Q5,IF(AND($AE5>=$S5,$AE5<=IF($V5=0,$AE5,$V5)),T5,IF(AND($AE5>=$V5,$AE5<=IF($Y5=0,$AE5,$Y5)),W5,IF(AND($AE5>=$Y5,$AE5<=IF($AB5=0,$AE5,$AB5)),Z5,IF($AE5>=$AB5,AC5,"error"))))))))))​
[/td][td]
=+AF5/AE5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]00000441[/td][td]Bean GYM [/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 11,888​
[/td][td]
=$AE6*IF($AE6<$D6,0,IF(OR(AND($D6=1,$G6=0),AND($AE6>=$D6,$AE6<=IF($G6=0,$AE6,$G6))),E6,IF(AND($AE6>=$G6,$AE6<=IF($J6=0,$AE6,$J6)),H6,IF(AND($AE6>=$J6,$AE6<=IF($M6=0,$AE6,$M6)),K6,IF(AND($AE6>=$M6,$AE6<=IF($P6=0,$AE6,$P6)),N6,IF(AND($AE6>=$P6,$AE6<=IF($S6=0,$AE6,$S6)),Q6,IF(AND($AE6>=$S6,$AE6<=IF($V6=0,$AE6,$V6)),T6,IF(AND($AE6>=$V6,$AE6<=IF($Y6=0,$AE6,$Y6)),W6,IF(AND($AE6>=$Y6,$AE6<=IF($AB6=0,$AE6,$AB6)),Z6,IF($AE6>=$AB6,AC6,"error"))))))))))​
[/td][td]
=+AF6/AE6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]00000380[/td][td]Gallery GYM [/td][td][/td][td]
$ 1​
[/td][td]
0.5%​
[/td][td][/td][td]
$ 2,200,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 2,700,000​
[/td][td]
4.0%​
[/td][td][/td][td]
$ 3,500,000​
[/td][td]
5.3%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 1,290,429​
[/td][td]
=$AE7*IF($AE7<$D7,0,IF(OR(AND($D7=1,$G7=0),AND($AE7>=$D7,$AE7<=IF($G7=0,$AE7,$G7))),E7,IF(AND($AE7>=$G7,$AE7<=IF($J7=0,$AE7,$J7)),H7,IF(AND($AE7>=$J7,$AE7<=IF($M7=0,$AE7,$M7)),K7,IF(AND($AE7>=$M7,$AE7<=IF($P7=0,$AE7,$P7)),N7,IF(AND($AE7>=$P7,$AE7<=IF($S7=0,$AE7,$S7)),Q7,IF(AND($AE7>=$S7,$AE7<=IF($V7=0,$AE7,$V7)),T7,IF(AND($AE7>=$V7,$AE7<=IF($Y7=0,$AE7,$Y7)),W7,IF(AND($AE7>=$Y7,$AE7<=IF($AB7=0,$AE7,$AB7)),Z7,IF($AE7>=$AB7,AC7,"error"))))))))))​
[/td][td]
=+AF7/AE7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]00000876[/td][td]Energy GYM [/td][td][/td][td]
$ 500,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 750,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 370,509​
[/td][td]
=$AE8*IF($AE8<$D8,0,IF(OR(AND($D8=1,$G8=0),AND($AE8>=$D8,$AE8<=IF($G8=0,$AE8,$G8))),E8,IF(AND($AE8>=$G8,$AE8<=IF($J8=0,$AE8,$J8)),H8,IF(AND($AE8>=$J8,$AE8<=IF($M8=0,$AE8,$M8)),K8,IF(AND($AE8>=$M8,$AE8<=IF($P8=0,$AE8,$P8)),N8,IF(AND($AE8>=$P8,$AE8<=IF($S8=0,$AE8,$S8)),Q8,IF(AND($AE8>=$S8,$AE8<=IF($V8=0,$AE8,$V8)),T8,IF(AND($AE8>=$V8,$AE8<=IF($Y8=0,$AE8,$Y8)),W8,IF(AND($AE8>=$Y8,$AE8<=IF($AB8=0,$AE8,$AB8)),Z8,IF($AE8>=$AB8,AC8,"error"))))))))))​
[/td][td]
=+AF8/AE8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]00000713[/td][td]Fiesta GYM [/td][td][/td][td]
$ 110,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 115,328​
[/td][td]
=$AE9*IF($AE9<$D9,0,IF(OR(AND($D9=1,$G9=0),AND($AE9>=$D9,$AE9<=IF($G9=0,$AE9,$G9))),E9,IF(AND($AE9>=$G9,$AE9<=IF($J9=0,$AE9,$J9)),H9,IF(AND($AE9>=$J9,$AE9<=IF($M9=0,$AE9,$M9)),K9,IF(AND($AE9>=$M9,$AE9<=IF($P9=0,$AE9,$P9)),N9,IF(AND($AE9>=$P9,$AE9<=IF($S9=0,$AE9,$S9)),Q9,IF(AND($AE9>=$S9,$AE9<=IF($V9=0,$AE9,$V9)),T9,IF(AND($AE9>=$V9,$AE9<=IF($Y9=0,$AE9,$Y9)),W9,IF(AND($AE9>=$Y9,$AE9<=IF($AB9=0,$AE9,$AB9)),Z9,IF($AE9>=$AB9,AC9,"error"))))))))))​
[/td][td]
=+AF9/AE9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]00000692[/td][td]Eagle GYM [/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 4,264​
[/td][td]
=$AE10*IF($AE10<$D10,0,IF(OR(AND($D10=1,$G10=0),AND($AE10>=$D10,$AE10<=IF($G10=0,$AE10,$G10))),E10,IF(AND($AE10>=$G10,$AE10<=IF($J10=0,$AE10,$J10)),H10,IF(AND($AE10>=$J10,$AE10<=IF($M10=0,$AE10,$M10)),K10,IF(AND($AE10>=$M10,$AE10<=IF($P10=0,$AE10,$P10)),N10,IF(AND($AE10>=$P10,$AE10<=IF($S10=0,$AE10,$S10)),Q10,IF(AND($AE10>=$S10,$AE10<=IF($V10=0,$AE10,$V10)),T10,IF(AND($AE10>=$V10,$AE10<=IF($Y10=0,$AE10,$Y10)),W10,IF(AND($AE10>=$Y10,$AE10<=IF($AB10=0,$AE10,$AB10)),Z10,IF($AE10>=$AB10,AC10,"error"))))))))))​
[/td][td]
=+AF10/AE10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]00000758[/td][td]Gain GYM [/td][td][/td][td]
$ 1​
[/td][td]
0.5%​
[/td][td][/td][td]
$ 150,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 200,000​
[/td][td]
1.5%​
[/td][td][/td][td]
$ 250,000​
[/td][td]
2.5%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 86,298​
[/td][td]
=$AE11*IF($AE11<$D11,0,IF(OR(AND($D11=1,$G11=0),AND($AE11>=$D11,$AE11<=IF($G11=0,$AE11,$G11))),E11,IF(AND($AE11>=$G11,$AE11<=IF($J11=0,$AE11,$J11)),H11,IF(AND($AE11>=$J11,$AE11<=IF($M11=0,$AE11,$M11)),K11,IF(AND($AE11>=$M11,$AE11<=IF($P11=0,$AE11,$P11)),N11,IF(AND($AE11>=$P11,$AE11<=IF($S11=0,$AE11,$S11)),Q11,IF(AND($AE11>=$S11,$AE11<=IF($V11=0,$AE11,$V11)),T11,IF(AND($AE11>=$V11,$AE11<=IF($Y11=0,$AE11,$Y11)),W11,IF(AND($AE11>=$Y11,$AE11<=IF($AB11=0,$AE11,$AB11)),Z11,IF($AE11>=$AB11,AC11,"error"))))))))))​
[/td][td]
=+AF11/AE11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]00000614[/td][td]Heat GYM [/td][td][/td][td]
$ 1​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 1,820,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 2,055,555​
[/td][td]
3.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 1,434,655​
[/td][td]
=$AE12*IF($AE12<$D12,0,IF(OR(AND($D12=1,$G12=0),AND($AE12>=$D12,$AE12<=IF($G12=0,$AE12,$G12))),E12,IF(AND($AE12>=$G12,$AE12<=IF($J12=0,$AE12,$J12)),H12,IF(AND($AE12>=$J12,$AE12<=IF($M12=0,$AE12,$M12)),K12,IF(AND($AE12>=$M12,$AE12<=IF($P12=0,$AE12,$P12)),N12,IF(AND($AE12>=$P12,$AE12<=IF($S12=0,$AE12,$S12)),Q12,IF(AND($AE12>=$S12,$AE12<=IF($V12=0,$AE12,$V12)),T12,IF(AND($AE12>=$V12,$AE12<=IF($Y12=0,$AE12,$Y12)),W12,IF(AND($AE12>=$Y12,$AE12<=IF($AB12=0,$AE12,$AB12)),Z12,IF($AE12>=$AB12,AC12,"error"))))))))))​
[/td][td]
=+AF12/AE12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]00000905[/td][td]Focus GYM [/td][td][/td][td]
$ 1​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 138,342​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 142,257​
[/td][td]
3.0%​
[/td][td][/td][td]
$ 146,173​
[/td][td]
4.0%​
[/td][td][/td][td]
$ 150,088​
[/td][td]
5.0%​
[/td][td][/td][td]
$ 156,614​
[/td][td]
6.0%​
[/td][td][/td][td]
$ 163,139​
[/td][td]
7.0%​
[/td][td][/td][td]
$ 169,665​
[/td][td]
8.0%​
[/td][td][/td][td]
$ 176,190​
[/td][td]
9.0%​
[/td][td][/td][td]
$ 108,360​
[/td][td]
=$AE13*IF($AE13<$D13,0,IF(OR(AND($D13=1,$G13=0),AND($AE13>=$D13,$AE13<=IF($G13=0,$AE13,$G13))),E13,IF(AND($AE13>=$G13,$AE13<=IF($J13=0,$AE13,$J13)),H13,IF(AND($AE13>=$J13,$AE13<=IF($M13=0,$AE13,$M13)),K13,IF(AND($AE13>=$M13,$AE13<=IF($P13=0,$AE13,$P13)),N13,IF(AND($AE13>=$P13,$AE13<=IF($S13=0,$AE13,$S13)),Q13,IF(AND($AE13>=$S13,$AE13<=IF($V13=0,$AE13,$V13)),T13,IF(AND($AE13>=$V13,$AE13<=IF($Y13=0,$AE13,$Y13)),W13,IF(AND($AE13>=$Y13,$AE13<=IF($AB13=0,$AE13,$AB13)),Z13,IF($AE13>=$AB13,AC13,"error"))))))))))​
[/td][td]
=+AF13/AE13​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]00000381[/td][td]Compound GYM [/td][td][/td][td]
$ 1​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 365,001​
[/td][td]
4.0%​
[/td][td][/td][td]
$ 415,001​
[/td][td]
4.5%​
[/td][td][/td][td]
$ 465,000​
[/td][td]
5.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 300,611​
[/td][td]
=$AE14*IF($AE14<$D14,0,IF(OR(AND($D14=1,$G14=0),AND($AE14>=$D14,$AE14<=IF($G14=0,$AE14,$G14))),E14,IF(AND($AE14>=$G14,$AE14<=IF($J14=0,$AE14,$J14)),H14,IF(AND($AE14>=$J14,$AE14<=IF($M14=0,$AE14,$M14)),K14,IF(AND($AE14>=$M14,$AE14<=IF($P14=0,$AE14,$P14)),N14,IF(AND($AE14>=$P14,$AE14<=IF($S14=0,$AE14,$S14)),Q14,IF(AND($AE14>=$S14,$AE14<=IF($V14=0,$AE14,$V14)),T14,IF(AND($AE14>=$V14,$AE14<=IF($Y14=0,$AE14,$Y14)),W14,IF(AND($AE14>=$Y14,$AE14<=IF($AB14=0,$AE14,$AB14)),Z14,IF($AE14>=$AB14,AC14,"error"))))))))))​
[/td][td]
=+AF14/AE14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]00000792[/td][td]Circulate GYM [/td][td][/td][td]
$ 1​
[/td][td]
0.5%​
[/td][td][/td][td]
$ 145,001​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 200,001​
[/td][td]
1.5%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 157,216​
[/td][td]
=$AE15*IF($AE15<$D15,0,IF(OR(AND($D15=1,$G15=0),AND($AE15>=$D15,$AE15<=IF($G15=0,$AE15,$G15))),E15,IF(AND($AE15>=$G15,$AE15<=IF($J15=0,$AE15,$J15)),H15,IF(AND($AE15>=$J15,$AE15<=IF($M15=0,$AE15,$M15)),K15,IF(AND($AE15>=$M15,$AE15<=IF($P15=0,$AE15,$P15)),N15,IF(AND($AE15>=$P15,$AE15<=IF($S15=0,$AE15,$S15)),Q15,IF(AND($AE15>=$S15,$AE15<=IF($V15=0,$AE15,$V15)),T15,IF(AND($AE15>=$V15,$AE15<=IF($Y15=0,$AE15,$Y15)),W15,IF(AND($AE15>=$Y15,$AE15<=IF($AB15=0,$AE15,$AB15)),Z15,IF($AE15>=$AB15,AC15,"error"))))))))))​
[/td][td]
=+AF15/AE15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]00000226[/td][td]GYMverse [/td][td][/td][td]
$ 100,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 200,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 300,000​
[/td][td]
3.0%​
[/td][td][/td][td]
$ 400,000​
[/td][td]
4.0%​
[/td][td][/td][td]
$ 500,000​
[/td][td]
5.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 161,796​
[/td][td]
=$AE16*IF($AE16<$D16,0,IF(OR(AND($D16=1,$G16=0),AND($AE16>=$D16,$AE16<=IF($G16=0,$AE16,$G16))),E16,IF(AND($AE16>=$G16,$AE16<=IF($J16=0,$AE16,$J16)),H16,IF(AND($AE16>=$J16,$AE16<=IF($M16=0,$AE16,$M16)),K16,IF(AND($AE16>=$M16,$AE16<=IF($P16=0,$AE16,$P16)),N16,IF(AND($AE16>=$P16,$AE16<=IF($S16=0,$AE16,$S16)),Q16,IF(AND($AE16>=$S16,$AE16<=IF($V16=0,$AE16,$V16)),T16,IF(AND($AE16>=$V16,$AE16<=IF($Y16=0,$AE16,$Y16)),W16,IF(AND($AE16>=$Y16,$AE16<=IF($AB16=0,$AE16,$AB16)),Z16,IF($AE16>=$AB16,AC16,"error"))))))))))​
[/td][td]
=+AF16/AE16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]00000126[/td][td]Fine GYM [/td][td][/td][td]
$ 1​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 2,260,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 2,900,000​
[/td][td]
3.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 1,511,282​
[/td][td]
=$AE17*IF($AE17<$D17,0,IF(OR(AND($D17=1,$G17=0),AND($AE17>=$D17,$AE17<=IF($G17=0,$AE17,$G17))),E17,IF(AND($AE17>=$G17,$AE17<=IF($J17=0,$AE17,$J17)),H17,IF(AND($AE17>=$J17,$AE17<=IF($M17=0,$AE17,$M17)),K17,IF(AND($AE17>=$M17,$AE17<=IF($P17=0,$AE17,$P17)),N17,IF(AND($AE17>=$P17,$AE17<=IF($S17=0,$AE17,$S17)),Q17,IF(AND($AE17>=$S17,$AE17<=IF($V17=0,$AE17,$V17)),T17,IF(AND($AE17>=$V17,$AE17<=IF($Y17=0,$AE17,$Y17)),W17,IF(AND($AE17>=$Y17,$AE17<=IF($AB17=0,$AE17,$AB17)),Z17,IF($AE17>=$AB17,AC17,"error"))))))))))​
[/td][td]
=+AF17/AE17​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18
[/td][td]00000018[/td][td]Sunrise GYM [/td][td][/td][td]
$ 65,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 80,000​
[/td][td]
4.0%​
[/td][td][/td][td]
$ 120,000​
[/td][td]
6.0%​
[/td][td][/td][td]
$ 150,000​
[/td][td]
8.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 870,991​
[/td][td]
=$AE18*IF($AE18<$D18,0,IF(OR(AND($D18=1,$G18=0),AND($AE18>=$D18,$AE18<=IF($G18=0,$AE18,$G18))),E18,IF(AND($AE18>=$G18,$AE18<=IF($J18=0,$AE18,$J18)),H18,IF(AND($AE18>=$J18,$AE18<=IF($M18=0,$AE18,$M18)),K18,IF(AND($AE18>=$M18,$AE18<=IF($P18=0,$AE18,$P18)),N18,IF(AND($AE18>=$P18,$AE18<=IF($S18=0,$AE18,$S18)),Q18,IF(AND($AE18>=$S18,$AE18<=IF($V18=0,$AE18,$V18)),T18,IF(AND($AE18>=$V18,$AE18<=IF($Y18=0,$AE18,$Y18)),W18,IF(AND($AE18>=$Y18,$AE18<=IF($AB18=0,$AE18,$AB18)),Z18,IF($AE18>=$AB18,AC18,"error"))))))))))​
[/td][td]
=+AF18/AE18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19
[/td][td]00000071[/td][td]GYMbea [/td][td][/td][td]
$ 1​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 250,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 270,000​
[/td][td]
3.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td]
$ -​
[/td][td]
0.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 132,689​
[/td][td]
=$AE19*IF($AE19<$D19,0,IF(OR(AND($D19=1,$G19=0),AND($AE19>=$D19,$AE19<=IF($G19=0,$AE19,$G19))),E19,IF(AND($AE19>=$G19,$AE19<=IF($J19=0,$AE19,$J19)),H19,IF(AND($AE19>=$J19,$AE19<=IF($M19=0,$AE19,$M19)),K19,IF(AND($AE19>=$M19,$AE19<=IF($P19=0,$AE19,$P19)),N19,IF(AND($AE19>=$P19,$AE19<=IF($S19=0,$AE19,$S19)),Q19,IF(AND($AE19>=$S19,$AE19<=IF($V19=0,$AE19,$V19)),T19,IF(AND($AE19>=$V19,$AE19<=IF($Y19=0,$AE19,$Y19)),W19,IF(AND($AE19>=$Y19,$AE19<=IF($AB19=0,$AE19,$AB19)),Z19,IF($AE19>=$AB19,AC19,"error"))))))))))​
[/td][td]
=+AF19/AE19​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20
[/td][td]00000377[/td][td]Falcon GYM [/td][td][/td][td]
$ 250,000​
[/td][td]
1.0%​
[/td][td][/td][td]
$ 420,000​
[/td][td]
1.5%​
[/td][td][/td][td]
$ 650,000​
[/td][td]
2.0%​
[/td][td][/td][td]
$ 850,000​
[/td][td]
2.5%​
[/td][td][/td][td]
$ 1,000,000​
[/td][td]
3.0%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
$ 807,810​
[/td][td]
=$AE20*IF($AE20<$D20,0,IF(OR(AND($D20=1,$G20=0),AND($AE20>=$D20,$AE20<=IF($G20=0,$AE20,$G20))),E20,IF(AND($AE20>=$G20,$AE20<=IF($J20=0,$AE20,$J20)),H20,IF(AND($AE20>=$J20,$AE20<=IF($M20=0,$AE20,$M20)),K20,IF(AND($AE20>=$M20,$AE20<=IF($P20=0,$AE20,$P20)),N20,IF(AND($AE20>=$P20,$AE20<=IF($S20=0,$AE20,$S20)),Q20,IF(AND($AE20>=$S20,$AE20<=IF($V20=0,$AE20,$V20)),T20,IF(AND($AE20>=$V20,$AE20<=IF($Y20=0,$AE20,$Y20)),W20,IF(AND($AE20>=$Y20,$AE20<=IF($AB20=0,$AE20,$AB20)),Z20,IF($AE20>=$AB20,AC20,"error"))))))))))​
[/td][td]
=+AF20/AE20​
[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Your help would be greatly appreciated.

Kind Regards

Biz
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I'm pretty new to MrExcel, so here's only a pic with a solution for you
1588180704009.png

It's simplified, with only three triggers.
The headers of the Trigger section are defined as a range TriggerHeads
R2=MATCH(R3,TriggerHeads,0), so it finds the column index with the Trigger1 numbers
S2, T2 similarly (you'll need more)
Trigger col finds the col of the trigger that you want. Array function!
You can then use that to do the calculations using INDEX
I've got to go now, but let me know if that helps.
 
Upvote 0
....R4 etc pull in the trigger numbers from the 1st, 4th and 7th columns.
0 in col V means no match
This whole thing is possible with no added columns if you need to do that, but it's much more opaque.
 
Upvote 0
Here's another option...give this a try to see if it gives the expected results. I left the AE column as the percentage so that you can confirm that the correct value is being returned. If this works, then just incorporate the multiplication by column AE.
Book20200428.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Vendor CodeVendorTrig1T1Trig2T2Trig3T3Trig4T4Trig5T5Trig6T6Trig7T7Trig8T8Trig9T9AmtCalc
25000001.50%7500003.00%9990004.50%0.00%0.00%4999990.00%
311.50%3.00%9990014.50%0.00%0.00%9990021.50%
45000001.50%7500003.00%9990004.50%0.00%0.00%5000011.50%
55000001.50%7500003.00%9990004.50%0.00%0.00%8000003.00%
65000001.50%7500003.00%9000004.50%9500004.70%1E+065.50%9700004.70%
75000001.50%6250003.00%7250004.50%7750004.60%8000004.80%8250005.00%8500005.25%8750005.50%9000005.75%8800005.50%
85000001.50%7500003.00%9990004.50%0.00%0.00%8000003.00%
Sheet7
Cell Formulas
RangeFormula
AF2:AF8AF2=IF(AND(D2=1,G2=0),E2,INDEX(D2:AC2,,MATCH(AGGREGATE(14,6,(LEFT($D$1:$AC$1,4)="Trig")*(D2:AC2<=AE2)*(D2:AC2),1),D2:AC2,0)+1))
 
Upvote 0
IF(AND(D2=1,G2=0),E2,INDEX(D2:AC2,,MATCH(AGGREGATE(14,6,(LEFT($D$1:$AC$1,4)="Trig")*(D2:AC2<=AE2)*(D2:AC2),1),D2:AC2,0)+1))

Hey Mate,

Very nicely done. I need to study it closely to understand the mechanics.

Kind Regards

Biz
 
Upvote 0
If it will help, I'll break down the various components of the formula:
IF(AND(D2=1,G2=0),E2,INDEX(D2:AC2,,MATCH(AGGREGATE(14,6,(LEFT($D$1:$AC$1,4)="Trig")*(D2:AC2<=AE2)*(D2:AC2),1),D2:AC2,0)+1))

(LEFT($D$1:$AC$1,4)="Trig") checks the contents of cells in the top row of the sheet from columns D to AC for anything beginning with "Trig". This creates an array consisting of TRUE and FALSE, every entry representing the comparison check for "Trig".

(D2:AC2<=AE2) similarly, this checks whether the AE value is greater than equal to values to the left in the same row, again producing an array of the same length as the LEFT function above, and the individual elements in the array are either TRUE or FALSE. This will find as TRUE even the percentage entries (we really don't want them, but for convenience, we accept those entries in this array). What we want to do here is ensure that we've identified all trigger thresholds where AE2 is greater than or equal to the trigger.

We multiply these two arrays together, which coerces the TRUE's and FALSE's to 1's and 0's. This multiplication is a dot product (1st element x 1st element, 2nd x 2nd, and so on), so we are left with a resultant array consisting of 0's and 1's, and now we've trimmed the array down to only those Trigger column values where AE2 is greater than or equal to them.

Then we multiply that resultant array of (1's and 0's) by (D2:AC2), and this isolates the actual Trigger values in the long array...nearly all elements are 0 except for the Trigger polnts that are less than or equal to AE2.
This array is operated on by the AGGREGATE function: AGGREGATE(14,6,array,1)
The "14" means we want a large value returned from that array, specifically the largest (that's what the "1" does). The 6 is another control that ignores errors, should the array contain elements that would crash the function.

So now we know the Trigger point value, but we don't yet know where it is found. Therefore, this entire max-finding function just described is placed inside a MATCH function:
MATCH(max-finding function ,D2:AC2,0) this tells us which column index relative to the starting D column that we find the max trigger value, and we want an exact match (the "0"). We know then, based on the logic in your original formula that we want the column just to the right of that trigger point (the percentage), so we add one to the column index, giving
MATCH(max-finding function ,D2:AC2,0) + 1

Then to retrieve the actual percentage value, we place this %-column-finding function in an INDEX function:
INDEX(D2:AC2, , %-column-finding function) this goes to the column index in the D2:AC2 array associated with the % value tied to the max trigger point meeting the AE2>=trigger criteria. Note the double comma in the function...that's not a typo, but rather a row designator, and since we're operating on the same row, it is left blank.

Finally, we have to deal with the exception involving what to do if D=1 and G=0...that condition isn't handled by the above, so we wrap the above algorithm in an IF statement:
IF(AND(D2=1,G2=0),E2, otherwise go the rest of the formula described above )

If AE2 is less than the starting point (column D), a 0 should be returned, so that component of your original formula doesn't have to be explicitly handled here (I think).

Let me know if you run into any surprises.
 
Upvote 0
Thank you very much for the explanation.

Biz
 
Upvote 0
Hi,

I have used search function so I can implement wildcards if required.


Before
(LEFT($D$1:$AC$1,4)="Trig")

After
SEARCH("Trig",$D$1:$AC$4)


Kind Regards

Biz
 
Upvote 0
Hi,
You could also add a bit of conditional formatting so that the active trigger in each row would change colour.
MrSyyr
 
Upvote 0
Thanks for the update, Biz. Yes, SEARCH should work fine and offers wildcard flexibility. With SEARCH, this is where the "6" option in the AGGREGATE functions becomes critical, as any column headings that do not meet the search criteria will return a #VALUE! error....which will simply be ignored.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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