Simplify formula

A

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
Hi,

After getting help for a formula to return the n last numerical value in a column, I am faced with a new problem. The sum of multiple differences, i.e., twenty in all, creates a formula that returns a "formula too large" message.

Does anyone have a way either using a UDF or cell formula to reduce the following cell formula to a working formula:

Code: 
=VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-2))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-3))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-4))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-5))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-6))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-7))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-8))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-9))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-10))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-11))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-12))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-13))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-14))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-15))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-16))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-17))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-18))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-20))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-21))

It's not really complicated, it just requires summing multiple instances of the last numerical value in a column minus the second to last, last numerical value in a column minus the third to last, last numerical value in a column minus the fourth to last, etc.ultimately going back twenty values in total.

I apologize for the long code listing. There may be a better way to show it but I don't know how.

A VBA solution is what I would expect to make this type of calculation with a loop. Does anyone he Forum have an idea on how to do this?

Thanks,

Art
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
artz said:
Hi,

After getting help for a formula to return the n last numerical value in a column, I am faced with a new problem. The sum of multiple differences, i.e., twenty in all, creates a formula that returns a "formula too large" message.

Does anyone have a way either using a UDF or cell formula to reduce the following cell formula to a working formula:

Code: 
=VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-2))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-3))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-4))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-5))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-6))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-7))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-8))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-9))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-10))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-11))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-12))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-13))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-14))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-15))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-16))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-17))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-18))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-20))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-21))

It's not really complicated, it just requires summing multiple instances of the last numerical value in a column minus the second to last, last numerical value in a column minus the third to last, last numerical value in a column minus the fourth to last, etc.ultimately going back twenty values in total.

I apologize for the long code listing. There may be a better way to show it but I don't know how.

A VBA solution is what I would expect to make this type of calculation with a loop. Does anyone he Forum have an idea on how to do this?

Thanks,

Art
Click to expand...
One way to significantly reduce the length of the formula is to replace all those references to 9.99999999999999E+307 and 9.99E+307 with 1E100. 1E100 will do the EXACT same thing and is a whole lot shorter and easier to remember.

Can you post some sample data and tell us what result you expect?
 
Upvote 0
How about LARGE()?

Something like this.....

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><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><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Values</td><td style="text-align: right;;"></td><td style=";">Order</td><td style=";">Largest</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">95</td><td style=";"><-- =LARGE($A$2:$A$40,C2)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">84</td><td style="text-align: right;;">11</td><td style=";"><-- =D$2-D3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">74</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">55</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">73</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">72</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">51</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">71</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">65</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">61</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">59</td><td style="text-align: right;;">36</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">47</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">58</td><td style="text-align: right;;">37</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">55</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td><td style="text-align: right;;">54</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td><td style="text-align: right;;">53</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;;">53</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;;">52</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">84</td><td style="text-align: right;;"></td><td style="text-align: right;;">16</td><td style="text-align: right;;">51</td><td style="text-align: right;;">44</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;">17</td><td style="text-align: right;;">50</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">61</td><td style="text-align: right;;"></td><td style="text-align: right;;">18</td><td style="text-align: right;;">47</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">19</td><td style="text-align: right;;">46</td><td style="text-align: right;;">49</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: right;;">46</td><td style="text-align: right;;">49</td><td style="text-align: right;;">681</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">74</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><td style=";">^-- =SUM(E3:E21)</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1219</td><td style=";"><-- =SUM(D2:D21)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">24</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1219</td><td style=";"><-- {=SUM(LARGE($A$2:$A$40,ROW(INDIRECT("1:20"))))}</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">     ^-- Enter array formula using Ctrl+Shift+Enter</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">681</td><td style=";"><-- {=SUM(LARGE($A$2:$A$40,1)-LARGE($A$2:$A$40,ROW(INDIRECT("2:20"))))}</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">25</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">59</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">43</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">58</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">53</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">30</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">34</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">14</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">46</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">95</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">53</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">72</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><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Let us know if this works for you.

Gary
 
Last edited:
Upvote 0
Or maybe this

=20*INDEX(A:A,MATCH(9.99E+307,A:A))-SUM(INDEX(A:A,MATCH(9.99E+307,A:A)-1):INDEX(A:A,MATCH(9.99E+307,A:A)-20))

HTH

M.
 
Upvote 0
Biff,

I have attempted to show the calculation I need in the following:

Excel Workbook
ABC
1DataDiffSum Diff
220.023.5513.0
321.022.5*
422.021.5*
523.020.5*
624.019.5*
725.018.5*
826.017.5*
927.016.5*
1028.015.5*
1129.014.5*
1230.013.5*
1331.012.5*
1430.013.5*
1529.014.5*
1628.015.5*
1727.016.5*
1826.017.5*
1925.018.5*
2024.019.5*
2123.020.5*
2222.021.5*
2321.022.5*
2424.019.5*
2527.016.5*
2628.515.0*
2730.013.5*
2831.512.0*
2933.010.5*
3034.59.0*
3136.07.5*
3237.56.0*
3339.04.5*
3440.53.0*
3542.0**
3643.5**
Sheet2




However a way to do this, i.e., cell formula, UDF, VBA code, I am fine with any solution.

Thanks,

Art
 
Upvote 0
Thought you want 20 terms

It's not really complicated, it just requires summing multiple instances of the last numerical value in a column minus the second to last, last numerical value in a column minus the third to last, last numerical value in a column minus the fourth to last, etc.ultimately going back twenty values in total.
Click to expand...

M.
 
Upvote 0
artz said:
Biff,

I have attempted to show the calculation I need in the following:

Excel Workbook
ABC
1DataDiffSum Diff
220.023.5513.0
321.022.5*
422.021.5*
523.020.5*
624.019.5*
725.018.5*
826.017.5*
927.016.5*
1028.015.5*
1129.014.5*
1230.013.5*
1331.012.5*
1430.013.5*
1529.014.5*
1628.015.5*
1727.016.5*
1826.017.5*
1925.018.5*
2024.019.5*
2123.020.5*
2222.021.5*
2321.022.5*
2424.019.5*
2527.016.5*
2628.515.0*
2730.013.5*
2831.512.0*
2933.010.5*
3034.59.0*
3136.07.5*
3237.56.0*
3339.04.5*
3440.53.0*
3542.0**
3643.5**
Sheet2




However a way to do this, i.e., cell formula, UDF, VBA code, I am fine with any solution.

Thanks,

Art
Click to expand...
Ok, can you explain how far back you want to go? In your other post you said:

ultimately going back twenty values in total.
Click to expand...
But in this posted sample you went back more than 20 values.

Also, I see you're starting from the 3rd to last value. Will there ALWAYS be at least 20 values to go back to?

This shouldn't be too difficult if we can get a definitive explanation of how it's supposed to work.
 
Upvote 0
Hi Biff,

Thanks for your response. I redid the worksheet example as shown. The first difference will always start from the second to last value (difference of the last value and second to last value) and will always cover twenty differences back from the last value in the column. Make sense?

Excel Workbook
ABC
1DataDiffSum Diff
220.0*283.5
321.0**
422.0**
523.0**
624.0**
725.0**
826.0**
927.0**
1028.0**
1129.0**
1230.0**
1331.0**
1430.0**
1529.0**
1628.0**
1727.014.5*
1826.015.5*
1925.016.5*
2024.017.5*
2123.018.5*
2222.019.5*
2321.020.5*
2424.021.5*
2527.022.5*
2628.519.5*
2730.016.5*
2831.515.0*
2933.013.5*
3034.512.0*
3136.010.5*
3237.59.0*
3339.07.5*
3440.56.0*
3542.04.5*
3643.53.0*
Sheet2


Is there an easy way like a code loop to do this?

Thanks,

Art
 
Upvote 0
artz said:
Hi Biff,

Thanks for your response. I redid the worksheet example as shown. The first difference will always start from the second to last value (difference of the last value and second to last value) and will always cover twenty differences back from the last value in the column. Make sense?

Excel Workbook
ABC
1DataDiffSum Diff
220.0*283.5
321.0**
422.0**
523.0**
624.0**
725.0**
826.0**
927.0**
1028.0**
1129.0**
1230.0**
1331.0**
1430.0**
1529.0**
1628.0**
1727.014.5*
1826.015.5*
1925.016.5*
2024.017.5*
2123.018.5*
2222.019.5*
2321.020.5*
2424.021.5*
2527.022.5*
2628.519.5*
2730.016.5*
2831.515.0*
2933.013.5*
3034.512.0*
3136.010.5*
3237.59.0*
3339.07.5*
3440.56.0*
3542.04.5*
3643.53.0*
Sheet2


Is there an easy way like a code loop to do this?

Thanks,

Art
Click to expand...
Try this array formula**:

=SUM(LOOKUP(1E100,A:A)-OFFSET(INDEX(A:A,MATCH(1E100,A:A)),-2,,-20))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Biff,

The array formula works great, thanks a lot.

Art
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Copy formula into a cell using vba
2
Replies
11
Views
864
BobtBuilder
B
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
244
Peter_SSs
Peter_SSs
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
532
awoohaw
awoohaw
P
  • Solved
Hard Question - Chatbot failed with multpile attempts
Replies
3
Views
222
pban
P
D
  • Question Question
What formula do i need vlookup to index match or something else?
Replies
8
Views
496
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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