Rebates Formula with Twist

[Biz]

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 Code	Vendor		Trigger 1	T1 %		Trigger 2	T2 %		Trigger 3	T3 %		Trigger 4	T4%		Trigger 5	T5 %		Trigger 6	T6 %		Trigger 7	T7 %		Trigger 8	T8 %		Trigger 9	T9 %		Amount	Calc
4​	00000390	Morning GYM		$ 500,000​	1.5%​		$ 750,000​	3.0%​		$ 999,000​	4.5%​		$ -​	0.0%​		$ -​	0.0%​														$ 176,526​	$ -​	0.0%​
5​	00000743	Olympia GYM		$ 1​	0.5%​		$ 80,000​	1.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 42,632​	$ 213​	0.5%​
6​	00000441	Bean GYM		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 11,888​	$ -​	0.0%​
7​	00000380	Gallery 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​	00000876	Energy GYM		$ 500,000​	1.0%​		$ 750,000​	2.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 370,509​	$ -​	0.0%​
9​	00000713	Fiesta GYM		$ 110,000​	1.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 115,328​	$ 1,153​	1.0%​
10​	00000692	Eagle GYM		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 4,264​	$ -​	0.0%​
11​	00000758	Gain GYM		$ 1​	0.5%​		$ 150,000​	1.0%​		$ 200,000​	1.5%​		$ 250,000​	2.5%​		$ -​	0.0%​														$ 86,298​	$ 431​	0.5%​
12​	00000614	Heat 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​	00000905	Focus 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​	00000381	Compound 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​	00000792	Circulate GYM		$ 1​	0.5%​		$ 145,001​	1.0%​		$ 200,001​	1.5%​		$ -​	0.0%​		$ -​	0.0%​														$ 157,216​	$ 1,572​	1.0%​
16​	00000226	GYMverse		$ 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​	00000126	Fine 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​	00000018	Sunrise 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​	00000071	GYMbea		$ 1​	1.0%​		$ 250,000​	2.0%​		$ 270,000​	3.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 132,689​	$ 1,327​	1.0%​
20​	00000377	Falcon 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

	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 Code	Vendor		Trigger 1	T1 %		Trigger 2	T2 %		Trigger 3	T3 %		Trigger 4	T4%		Trigger 5	T5 %		Trigger 6	T6 %		Trigger 7	T7 %		Trigger 8	T8 %		Trigger 9	T9 %		Amount	Calc
4​	00000390	Morning GYM		$ 500,000​	1.5%​		$ 750,000​	3.0%​		$ 999,000​	4.5%​		$ -​	0.0%​		$ -​	0.0%​														$ 176,526​	=$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"))))))))))​	=+AF4/AE4​
5​	00000743	Olympia GYM		$ 1​	0.5%​		$ 80,000​	1.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 42,632​	=$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"))))))))))​	=+AF5/AE5​
6​	00000441	Bean GYM		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 11,888​	=$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"))))))))))​	=+AF6/AE6​
7​	00000380	Gallery GYM		$ 1​	0.5%​		$ 2,200,000​	2.0%​		$ 2,700,000​	4.0%​		$ 3,500,000​	5.3%​		$ -​	0.0%​														$ 1,290,429​	=$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"))))))))))​	=+AF7/AE7​
8​	00000876	Energy GYM		$ 500,000​	1.0%​		$ 750,000​	2.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 370,509​	=$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"))))))))))​	=+AF8/AE8​
9​	00000713	Fiesta GYM		$ 110,000​	1.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 115,328​	=$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"))))))))))​	=+AF9/AE9​
10​	00000692	Eagle GYM		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 4,264​	=$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"))))))))))​	=+AF10/AE10​
11​	00000758	Gain GYM		$ 1​	0.5%​		$ 150,000​	1.0%​		$ 200,000​	1.5%​		$ 250,000​	2.5%​		$ -​	0.0%​														$ 86,298​	=$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"))))))))))​	=+AF11/AE11​
12​	00000614	Heat GYM		$ 1​	1.0%​		$ 1,820,000​	2.0%​		$ 2,055,555​	3.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 1,434,655​	=$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"))))))))))​	=+AF12/AE12​
13​	00000905	Focus 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​	=$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"))))))))))​	=+AF13/AE13​
14​	00000381	Compound GYM		$ 1​	2.0%​		$ 365,001​	4.0%​		$ 415,001​	4.5%​		$ 465,000​	5.0%​		$ -​	0.0%​														$ 300,611​	=$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"))))))))))​	=+AF14/AE14​
15​	00000792	Circulate GYM		$ 1​	0.5%​		$ 145,001​	1.0%​		$ 200,001​	1.5%​		$ -​	0.0%​		$ -​	0.0%​														$ 157,216​	=$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"))))))))))​	=+AF15/AE15​
16​	00000226	GYMverse		$ 100,000​	1.0%​		$ 200,000​	2.0%​		$ 300,000​	3.0%​		$ 400,000​	4.0%​		$ 500,000​	5.0%​														$ 161,796​	=$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"))))))))))​	=+AF16/AE16​
17​	00000126	Fine GYM		$ 1​	1.0%​		$ 2,260,000​	2.0%​		$ 2,900,000​	3.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 1,511,282​	=$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"))))))))))​	=+AF17/AE17​
18​	00000018	Sunrise GYM		$ 65,000​	2.0%​		$ 80,000​	4.0%​		$ 120,000​	6.0%​		$ 150,000​	8.0%​		$ -​	0.0%​														$ 870,991​	=$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"))))))))))​	=+AF18/AE18​
19​	00000071	GYMbea		$ 1​	1.0%​		$ 250,000​	2.0%​		$ 270,000​	3.0%​		$ -​	0.0%​		$ -​	0.0%​														$ 132,689​	=$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"))))))))))​	=+AF19/AE19​
20​	00000377	Falcon GYM		$ 250,000​	1.0%​		$ 420,000​	1.5%​		$ 650,000​	2.0%​		$ 850,000​	2.5%​		$ 1,000,000​	3.0%​														$ 807,810​	=$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"))))))))))​	=+AF20/AE20​

Sheet: Sheet1

Your help would be greatly appreciated.

Kind Regards

Biz

 

[MrSyyr]

I'm pretty new to MrExcel, so here's only a pic with a solution for you

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.

 

[MrSyyr]

....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.

 

[KRice]

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
	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
1	Vendor Code	Vendor		Trig1	T1		Trig2	T2		Trig3	T3		Trig4	T4		Trig5	T5		Trig6	T6		Trig7	T7		Trig8	T8		Trig9	T9		Amt	Calc
2				500000	1.50%		750000	3.00%		999000	4.50%			0.00%			0.00%														499999	0.00%
3				1	1.50%			3.00%		999001	4.50%			0.00%			0.00%														999002	1.50%
4				500000	1.50%		750000	3.00%		999000	4.50%			0.00%			0.00%														500001	1.50%
5				500000	1.50%		750000	3.00%		999000	4.50%			0.00%			0.00%														800000	3.00%
6				500000	1.50%		750000	3.00%		900000	4.50%		950000	4.70%		1E+06	5.50%														970000	4.70%
7				500000	1.50%		625000	3.00%		725000	4.50%		775000	4.60%		800000	4.80%		825000	5.00%		850000	5.25%		875000	5.50%		900000	5.75%		880000	5.50%
8				500000	1.50%		750000	3.00%		999000	4.50%			0.00%			0.00%														800000	3.00%
Sheet7

Cell Formulas
Range	Formula
AF2:AF8	AF2	=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))

 

[Biz]

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

 

[KRice]

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.

 

[Biz]

Thank you very much for the explanation.

Biz

 

[Biz]

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

 

[MrSyyr]

Hi,
You could also add a bit of conditional formatting so that the active trigger in each row would change colour.
MrSyyr

 

[KRice]

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.