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
Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
Your help would be greatly appreciated.
Kind Regards
Biz
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
[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