Find high low values in diffrent cycles

C

capex

New Member
Joined
Apr 18, 2009
Messages
44
Hi,

I am unable to find proper solution to following problem, any help is appreciated. Thank you for your time.

Problem:

1. In example provided Column A has A5 as highest point and A37 is lowest, this would be cycle 1 and I want to calculate % difference between this two point which is -1.65% placed in cycle 1(B2).
2. Cycle 2 started as value in cell A45 exceeded value in A5, this is a beginning of new cycle 2, till that time cycle 1 continues. I want to calculate % difference between high and low point again and place it in Cycle 2 (C2).
3. Cycle three has started from A53 since it exceeded A45.

Example:

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Values</td><td style="text-align: center;background-color: #FFFF00;;">Cycle 1</td><td style="text-align: center;background-color: #92D050;;">Cycle 2</td><td style="text-align: center;;">Cycle 3 </td><td style="text-align: center;;">Cycle 4</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">$151,658.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">$151,766.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">$151,836.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FFFF00;;">$152,053.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #FFFF00;;">$151,903.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFF00;;">$151,215.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFF00;;">$151,234.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #FFFF00;;">$151,392.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #FFFF00;;">$151,770.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #FFFF00;;">$151,573.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #FFFF00;;">$150,780.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #FFFF00;;">$150,202.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #FFFF00;;">$151,222.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;background-color: #FFFF00;;">$150,649.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;background-color: #FFFF00;;">$151,551.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;background-color: #FFFF00;;">$152,051.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;background-color: #FFFF00;;">$150,501.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;background-color: #FFFF00;;">$150,614.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;background-color: #FFFF00;;">$150,688.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;background-color: #FFFF00;;">$150,379.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;background-color: #FFFF00;;">$150,564.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;background-color: #FFFF00;;">$150,619.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;background-color: #FFFF00;;">$150,665.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;background-color: #FFFF00;;">$150,435.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;background-color: #FFFF00;;">$150,109.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;background-color: #FFFF00;;">$149,925.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;background-color: #FFFF00;;">$150,112.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;background-color: #FFFF00;;">$149,973.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;background-color: #FFFF00;;">$150,253.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;background-color: #FFFF00;;">$150,074.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;background-color: #FFFF00;;">$149,846.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;background-color: #FFFF00;;">$149,871.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;background-color: #FFFF00;;">$149,770.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;background-color: #FFFF00;;">$149,810.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;background-color: #FFFF00;;">$150,063.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;background-color: #FFFF00;;">$149,582.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">$149,972.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">$150,009.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">$150,298.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">$150,873.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">$151,024.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">$151,248.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">$151,321.50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;background-color: #92D050;;">$156,600.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;background-color: #92D050;;">$156,400.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;background-color: #92D050;;">$155,000.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;background-color: #92D050;;">$154,900.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;background-color: #92D050;;">$155,000.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;background-color: #92D050;;">$153,000.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;background-color: #92D050;;">$153,500.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;background-color: #92D050;;">$152,400.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">53</td><td style="text-align: right;;">$157,000.00</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>
Sheet2​
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hi capex,

If I understand your definitions of each cycle, I believe the formulas shown below will work.

I'd suggest that you add some helper rows as shown.
I've moved the data range to A9:A60 to make room for these rows.
It's possible to roll these rows up into one cell for each cycle's Pct Change,
however the formulas will grow with each cycle and quickly become unwieldy.

Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 138px"><COL style="WIDTH: 100px"><COL style="WIDTH: 95px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Cycle 1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Cycle 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Low Value</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$149,582.50 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$152,400.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Low Week</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">36</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">51</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">High Value</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$152,053.00 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$156,600.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">High Week</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">44</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Pct Change</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-1.65%</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-2.76%</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Values</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$151,658.00 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$151,766.00 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$151,836.00 </TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=MIN($A$9:$A$60)</TD></TR><TR><TD>C2</TD><TD>=MIN(OFFSET($A$9,C5,0,ROWS($A$9:$A$60)-C5))</TD></TR><TR><TD>B3</TD><TD>=MATCH(B$2,$A$9:$A$60,0)</TD></TR><TR><TD>C3</TD><TD>=C5-1+MATCH(C$2,OFFSET($A$9,C5-1,0,ROWS($A$9:$A$60)-C5),0)</TD></TR><TR><TD>B4</TD><TD>=MAX(OFFSET($A$9,0,0,$B$3,1))</TD></TR><TR><TD>C4</TD><TD>=INDEX($A$9:$A$60,C5)</TD></TR><TR><TD>B5</TD><TD>=MATCH(B4,$A$9:$A$60,0)</TD></TR><TR><TD>C5</TD><TD>{=MATCH(TRUE,$A$9:$A$60>B$4,0)}</TD></TR><TR><TD>B6</TD><TD>=1-(B$4/B$2)</TD></TR><TR><TD>C6</TD><TD>=1-(C$4/C$2)</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

The formulas for Cycle 2 could be copied to additional columns for Cycles 3,4,5...
The formulas for Cycle 1 are slightly different because they are not defined
by a previous cycle's high value.
 
Last edited:
Upvote 0
Hi, JS411,

Excellent work, it worked very well. It is amazing how you guys figure out things like this, wonderful. Thank you for your time and help, I appreciate it. Thank you.
 
Upvote 0
HI, JS411

Your solution worked very well for me but got in to minor problems.

1. In first shot, Column S cell numbers are off by one, 23 needs to be 24 and 17 needs to be 18. Played around but could not figure that out.
2. Is there a way to show related month which is in column H instead of cell number or both?
3. In Column R, Max cell number 2 is also off by one, should be 3.

Thank you very much for your time and help.


Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>Q</th><th>R</th><th>S</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">40</td><td style=";">Min</td><td style="text-align: right;;">$140,113</td><td style="text-align: right;;">$198,481</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style=";">Cell</td><td style="text-align: right;;">14</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style=";">Max</td><td style="text-align: right;;">$155,228</td><td style="text-align: right;;">$202,916</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style=";">Cell</td><td style="text-align: right;;">2</td><td style="text-align: right;;">17</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style=";">Percent</td><td style="text-align: right;;">-10.79%</td><td style="text-align: right;;">-2.23%</td></tr></tbody></table>
Performance Analysis​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R40</th><td style="text-align:left">=MIN(Drawdown!$I$2:$I25)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S40</th><td style="text-align:left">=MIN(OFFSET(Drawdown!$I$2,S43,0,ROWS(Drawdown!$I$2:$I25)-S43))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R41</th><td style="text-align:left">=MATCH(R$40,Drawdown!$I$2:$I25,0)+1</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S41</th><td style="text-align:left">=S43-1+MATCH(S$40,OFFSET(Drawdown!$I$2,S43-1,0,ROWS(Drawdown!$I$2:$I25)-S43),0)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R42</th><td style="text-align:left">=MAX(OFFSET(Drawdown!$I$2,0,0,$R$41,1))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S42</th><td style="text-align:left">=INDEX(Drawdown!$I$2:$I25,S43)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R43</th><td style="text-align:left">=MATCH(R42,Drawdown!$I$2:I25,0)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R44</th><td style="text-align:left">=1-(R$42/R$40)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S44</th><td style="text-align:left">=1-(S$42/S$40)</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S43</th><td style="text-align:left">{=MATCH(TRUE,Drawdown!$I$2:$I25>R$42,0)}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>H</th><th>I</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Months</td><td style=";">JP</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8/1/2009</td><td style="text-align: right;;">155211</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9/1/2009</td><td style="text-align: right;;">155228</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10/1/2009</td><td style="text-align: right;;">155058</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">11/1/2009</td><td style="text-align: right;;">148583</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">12/1/2009</td><td style="text-align: right;;">147975</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;">144151</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2/1/2010</td><td style="text-align: right;;">144071</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">3/1/2010</td><td style="text-align: right;;">144845</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4/1/2010</td><td style="text-align: right;;">144754</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5/1/2010</td><td style="text-align: right;;">143633</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">6/1/2010</td><td style="text-align: right;;">140768</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">7/1/2010</td><td style="text-align: right;;">141947</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8/1/2010</td><td style="text-align: right;;">140113</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9/1/2010</td><td style="text-align: right;;">144827</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10/1/2010</td><td style="text-align: right;;">147387</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">11/1/2010</td><td style="text-align: right;;">147219</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">202916</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">201496</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">2/1/2011</td><td style="text-align: right;;">204553</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">3/1/2011</td><td style="text-align: right;;">203566</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">4/1/2011</td><td style="text-align: right;;">209923</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">5/1/2011</td><td style="text-align: right;;">202154</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">6/1/2011</td><td style="text-align: right;;">198481</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">7/1/2011</td><td style="text-align: right;;">200640</td></tr></tbody></table>
Drawdown​
 
Last edited:
Upvote 0
capex said:
1. In first shot, Column S cell numbers are off by one, 23 needs to be 24 and 17 needs to be 18. Played around but could not figure that out.
2. Is there a way to show related month which is in column H instead of cell number or both?
3. In Column R, Max cell number 2 is also off by one, should be 3.
Click to expand...

The "cell numbers" are row numbers relative to the data range - not relative to the worksheet. The 3 cells you noted are actually correct. The one that looked was correct was actually off by one! :oops: I've fixed that formula below.

You could display the row number, however that makes the formulas that use these cells in Index formulas a bit more complicated. Hopefully by displaying the months it will be easier to read.

Performance Analysis

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 73px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>Q</TD><TD>R</TD><TD>S</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Min</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$140,113 </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$198,481 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Month</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8/1/2010</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">6/1/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Index</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">13 </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Max</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$155,228 </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$202,916 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Month</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9/1/2009</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12/1/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Index</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">17</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Percent</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-10.79%</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-2.23%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>R40</TD><TD>=MIN(Drawdown!$I$2:$I$25)</TD></TR><TR><TD>S40</TD><TD>=MIN(OFFSET(Drawdown!$I$2,S$45,0,ROWS(Drawdown!$I$2:$I$25)-S$45))</TD></TR><TR><TD>R41</TD><TD>=OFFSET(Drawdown!$I$2:$I$25,$R$42-1,-1,1,1)</TD></TR><TR><TD>S41</TD><TD>=OFFSET(Drawdown!$I$2:$I$25,S$42-1,-1,1,1)</TD></TR><TR><TD>R42</TD><TD>=MATCH($R$40,Drawdown!$I$2:$I$25,0)</TD></TR><TR><TD>S42</TD><TD>=S$45-1+MATCH(S$40,OFFSET(Drawdown!$I$2,S$45-1,0,ROWS(Drawdown!$I$2:$I$25)-S$45),0)</TD></TR><TR><TD>R43</TD><TD>=MAX(OFFSET(Drawdown!$I$2,0,0,$R$42,1))</TD></TR><TR><TD>S43</TD><TD>=INDEX(Drawdown!$I$2:$I$25,S$45)</TD></TR><TR><TD>R44</TD><TD>=OFFSET(Drawdown!$I$2:$I$25,$R$45-1,-1,1,1)</TD></TR><TR><TD>S44</TD><TD>=OFFSET(Drawdown!$I$2:$I$25,S$45-1,-1,1,1)</TD></TR><TR><TD>R45</TD><TD>=MATCH($R$43,Drawdown!$I$2:$I$25,0)</TD></TR><TR><TD>S45</TD><TD>{=MATCH(TRUE,Drawdown!$I$2:$I25>R$43,0)}</TD></TR><TR><TD>R46</TD><TD>=1-(R$43/R$40)</TD></TR><TR><TD>S46</TD><TD>=1-(S$43/S$40)</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
692
John_w
J

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

Share this page

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