12-month Rolling Difference

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
I am in need of a two solutions that will compute the difference between two numbers.

First Problem: I have a table with hour meter readings in rows representing different equipment. The columns represent the end of month reads with the oldest to the left and newest towards the right. I need to compute the 12 month rolling difference between the most current entry and the entry 11 columns to the left.

The formula I created seems to do this fine in Excel 2003:

=IF(COUNT($CT6:$EH6)<1,"",IF(ISERROR(LOOKUP(9.9E+307,$CT6:$EH6)-LOOKUP(9.99E+307,$CT6:INDEX($CT6:$EH6,MATCH(9.99E+307,$CT6:$EH6)-11))),MAX($CT6:$EH6)-MIN($CT6:$EH6),LOOKUP(9.9E+307,$CT6:$EH6)-LOOKUP(9.99E+307,$CT6:INDEX($CT6:$EH6,MATCH(9.99E+307,$CT6:$EH6)-11))))

DQ DS DT DU DV DW DX DY DZ EA EB EC FR
670 100 200 300 400 500 600 700 800 900 1,000 1,100 430

In the example above, a value is missing in 11th column to the left of the last value (1,100). The formula above calculates 430, while I am expecting 1,100 – 100 = 1000. If the 670 was not there, then it seems to work. The actual example is in row 13 shown below. My intent is to take the last available number (in this case 1,100) and subtract from it the first available number to the left and within 11 columns. So if there were only 3 numbers as shown below, the answer should be 700.

670 (blank columns)... 300 (blank columns)... 800 1,100 700

The numbers are supposed to always increase toward the right, but sometimes they don’t. So in other words, I want to find the difference in two numbers and force Excel to only pick from the 11 column range preceding the last value in the range. The last number in range minus the first available number in 11-column range.

The Second Problem: I need to expand to the entire range (columns H through EC) and for each row, find the highest or maximum difference in any 12 column range – sort of a rolling 12-month (12- column) difference and next to that value the address of the right most value used in calculating that maximum.

Excel Workbook
DPDQDRDSDTDUDVDWDXDYDZEAEBECFR
4Nov 2013Dec 2013Jan 2014Feb 2014Mar 2014Apr 2014May 2014Jun 2014Jul 2014Aug 2014Sep 2014Oct 2014Nov 2014Dec 2014Rolling 12 Month
5***************
65,3345,5195,7325,9106,1246,2686,5426,6596,6596,6596,6596,8847,1917,5721,840
74,9914,9914,9914,9914,9914,9915,1745,1765,3775,5015,7675,9396,0076,0581,067
88,4798,4898,5228,5708,7198,8078,9979,1469,2989,4969,6079,8219,92610,0511,529
910,95210,95210,95210,95410,95310,95310,95310,95910,95910,96110,96210,963966967-9,985
1020,41020,55020,63320,73220,75720,78720,88520,98020,98021,17021,30421,32921,32021,329696
1116,38116,44416,52316,80316,92116,98817,15017,24117,34417,48517,63217,79618,00018,2241,701
127,5377,6677,7347,9377,9848,0838,1418,1418,1418,1438,1448,2728,3938,393659
13*670*1002003004005006007008009001,0001,100430
INPUT





Thank you.
 
Agreed. I believe you hit it spot on when you brought up the point about empty cells or text. While there is no text the empty cells could be the issue. Also you are correct about any ties in maximums, that I would like the most recent (the 12-month group nearest to the last column) maximum case. I will try your updated formulas and get back. In the meantime it would be nice if one or more other experts chimmed in as this is quite a complex problem.
 
Upvote 0
[TABLE="width: 961"]
<TBODY>[TR]
[TD]DJ</SPAN></SPAN>
[/TD]
[TD]DK </SPAN></SPAN>
[/TD]
[TD]DL </SPAN></SPAN>
[/TD]
[TD]DM </SPAN></SPAN>
[/TD]
[TD]DN</SPAN></SPAN>
[/TD]
[TD]DO</SPAN></SPAN>
[/TD]
[TD]DP</SPAN></SPAN>
[/TD]
[TD]DQ</SPAN></SPAN>
[/TD]
[TD]DR</SPAN></SPAN>
[/TD]
[TD]DS </SPAN></SPAN>
[/TD]
[TD]DT</SPAN></SPAN>
[/TD]
[TD]DU</SPAN></SPAN>
[/TD]
[TD]DV </SPAN></SPAN>
[/TD]
[TD]DW</SPAN></SPAN>
[/TD]
[TD]DX</SPAN></SPAN>
[/TD]
[TD]DY</SPAN></SPAN>
[/TD]
[TD]DZ</SPAN></SPAN>
[/TD]
[TD]EA</SPAN></SPAN>
[/TD]
[TD]EB </SPAN></SPAN>
[/TD]
[TD]EC </SPAN></SPAN>
[/TD]
[TD]ED</SPAN></SPAN>
[/TD]
[TD]EE</SPAN></SPAN>
[/TD]
[TD]EF </SPAN></SPAN>
[/TD]
[TD]EG </SPAN></SPAN>
[/TD]
[TD]EH</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Apr 2013</SPAN></SPAN>
[/TD]
[TD]May 2013</SPAN></SPAN>
[/TD]
[TD]Jun 2013</SPAN></SPAN>
[/TD]
[TD]Jul 2013</SPAN></SPAN>
[/TD]
[TD]Aug 2013</SPAN></SPAN>
[/TD]
[TD]Sep 2013</SPAN></SPAN>
[/TD]
[TD]Oct 2013</SPAN></SPAN>
[/TD]
[TD]Nov 2013</SPAN></SPAN>
[/TD]
[TD]Dec 2013</SPAN></SPAN>
[/TD]
[TD]Jan 2014</SPAN></SPAN>
[/TD]
[TD]Feb 2014</SPAN></SPAN>
[/TD]
[TD]Mar 2014</SPAN></SPAN>
[/TD]
[TD]Apr 2014</SPAN></SPAN>
[/TD]
[TD]May 2014</SPAN></SPAN>
[/TD]
[TD]Jun 2014</SPAN></SPAN>
[/TD]
[TD]Jul 2014</SPAN></SPAN>
[/TD]
[TD]Aug 2014</SPAN></SPAN>
[/TD]
[TD]Sep 2014</SPAN></SPAN>
[/TD]
[TD]Oct 2014</SPAN></SPAN>
[/TD]
[TD]Nov 2014</SPAN></SPAN>
[/TD]
[TD]Dec 2014</SPAN></SPAN>
[/TD]
[TD]Jan 2015</SPAN></SPAN>
[/TD]
[TD]Feb 2015</SPAN></SPAN>
[/TD]
[TD]Mar 2015</SPAN></SPAN>
[/TD]
[TD]Apr 2015</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3,424</SPAN></SPAN>
[/TD]
[TD]3,648</SPAN></SPAN>
[/TD]
[TD]3,916</SPAN></SPAN>
[/TD]
[TD]4,159</SPAN></SPAN>
[/TD]
[TD]4,483</SPAN></SPAN>
[/TD]
[TD]4,728</SPAN></SPAN>
[/TD]
[TD]5,061</SPAN></SPAN>
[/TD]
[TD]5,334</SPAN></SPAN>
[/TD]
[TD]5,519</SPAN></SPAN>
[/TD]
[TD]5,732</SPAN></SPAN>
[/TD]
[TD]5,910</SPAN></SPAN>
[/TD]
[TD]6,124</SPAN></SPAN>
[/TD]
[TD]6,268</SPAN></SPAN>
[/TD]
[TD]6,542</SPAN></SPAN>
[/TD]
[TD]6,659</SPAN></SPAN>
[/TD]
[TD]6,659</SPAN></SPAN>
[/TD]
[TD]6,659</SPAN></SPAN>
[/TD]
[TD]6,659</SPAN></SPAN>
[/TD]
[TD]6,884</SPAN></SPAN>
[/TD]
[TD]7,191</SPAN></SPAN>
[/TD]
[TD]7,572</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Results in the order of the 8 formulas: </SPAN></SPAN>

2700 1 117 DU 6124 117 DU 6124

Testing on the first row 6, the first result of 2700 is correct, second one also correct. I think the 3rd</SPAN> , 4th</SPAN> and 5th</SPAN> formulas repeat the 6th</SPAN> ,7th</SPAN> and 8th</SPAN> so the answer to the third, fourth and fifth formulas should be 106, DJ and 3,424. The 7th</SPAN> and 8th</SPAN> are correct.

Again thanks for the tremendous help.</SPAN></SPAN>
 
Upvote 0
The two groups of formulas (3rd, 4th, & 5th; 6th, 7th, & 8th) should return the same answers if there is only one true maximum! The result of the 2nd formula is telling you that, for this data sample, there is only one 12-month period when the maximum usage occurred.

If there are 2 or more 12 month periods that happen to have the same usage and that usage is also the maximum (so, one maximum usage amount, but more than one 12-month period that match that level of usage), the first group (3rd, 4th, & 5th) of formulas will tell you the location (position within vector array and corresponding Excel column label) and the ending (right-most) meter reading for the earliest 12-month period that matches that maximum level of usage. The second group (6th, 7th, & 8th) will tell you the location and and ending metering reading for the latest 12-month period that matches that maximum level of usage.

Based on your latest reply, it seems as tho you expected the first group of formulas to return the starting (left-most) meter reading for the latest 12-month period that matches that maximum level of usage, which is a different result from what I thought you were asking for in your original post. You can adapt the formulas I gave you to give that answer tho (just adjust the offsets).

I'm sorry if I confused you by providing 2 sets of formulas in response to your Second Problem - I just thought it might be helpful. If ties are possible, but you only need the answers for the latest period, use the 1st (the maximum usage amount), 7th (column of the ending meter reading), and 8th (ending meter reading) formulas to satisfy the information you sought for the Second Problem in your original post.

There are probably far more efficient solutions....

SDL

SDL
 
Upvote 0

Forum statistics

Threads
1,226,868
Messages
6,193,435
Members
453,799
Latest member
shanley ducker

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