12-month Rolling Difference

[dwgnome]

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
	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	FR
4	Nov 2013	Dec 2013	Jan 2014	Feb 2014	Mar 2014	Apr 2014	May 2014	Jun 2014	Jul 2014	Aug 2014	Sep 2014	Oct 2014	Nov 2014	Dec 2014	Rolling 12 Month
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	5,334	5,519	5,732	5,910	6,124	6,268	6,542	6,659	6,659	6,659	6,659	6,884	7,191	7,572	1,840
7	4,991	4,991	4,991	4,991	4,991	4,991	5,174	5,176	5,377	5,501	5,767	5,939	6,007	6,058	1,067
8	8,479	8,489	8,522	8,570	8,719	8,807	8,997	9,146	9,298	9,496	9,607	9,821	9,926	10,051	1,529
9	10,952	10,952	10,952	10,954	10,953	10,953	10,953	10,959	10,959	10,961	10,962	10,963	966	967	-9,985
10	20,410	20,550	20,633	20,732	20,757	20,787	20,885	20,980	20,980	21,170	21,304	21,329	21,320	21,329	696
11	16,381	16,444	16,523	16,803	16,921	16,988	17,150	17,241	17,344	17,485	17,632	17,796	18,000	18,224	1,701
12	7,537	7,667	7,734	7,937	7,984	8,083	8,141	8,141	8,141	8,143	8,144	8,272	8,393	8,393	659
13	*	670	*	100	200	300	400	500	600	700	800	900	1,000	1,100	430
INPUT

Thank you.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[SLARRA]

I'm hardly an expert, but.... With regard to your First Problem, specifically the bit regarding the meter reading from a year ago - I think the formula you posted finds the last cell having a number and lying within the array that starts at the beginning of the overall range (column CT) and ends with the first cell in the 11 column-wide array whose endpoint is based on the last column that has a number within the overall range (i.e. the most recent meter reading). In row 13, the absence of a number in column DR results in 670, the number in DQ being used.

I think you wanted to identify the first cell that has a number and lies within in the 11-column array.

Tho you'll have to add back your IFs that trap errors, I think this might resolve that First Problem and its missing reading in row 13 (note: it's an array formula, so use CTL+SHIFT+ENTER to save it):

=INDEX($CT13:$EH13,MATCH(9.99999999999999E+307,$CT13:$EH13))-INDEX($CT13:$EH13,MATCH(TRUE,ISNUMBER(OFFSET($CT13,0,(MATCH(9.99999999999999E+307,$CT13:$EH13)-12),,11)),0)+(MATCH(9.99999999999999E+307,$CT13:$EH13)-12))

I'll apologize now in advance if I have bungled this and added to your woes - as I hinted above, my "expertise" is laughable compared to the people who regularly provide advice. Since no one had helped you yet, I thought I'd give it a go...

SDL

 

[dwgnome]

Thank you SDL, that worked perfectly. You really understood my problem.
Could you explain what your formula is doing step by step. I understand the first part where the first INDEX/MATCH finds the last number in the row, but I get lost on the second INDEX/MATCH and extra +MATCH.

In the meantime, it would be great if someone can attempt a solution to the second problem outlined in my original post.

 

[SLARRA]

Glad it worked!

Here's the sample formula again:
=INDEX($CT13:$EH13,MATCH(9.99999999999999E+307,$CT13:$EH13))-INDEX($CT13:$EH13,MATCH(TRUE,ISNUMBER(OFFSET($CT13,0,(MATCH(9.99999999999999E+307,$CT13:$EH13)-12),,11)),0)+(MATCH(9.99999999999999E+307,$CT13:$EH13)-12))

And an explanation of the relevant bits:

Position of last reading: MATCH(9.99999999999999E+307,$CT13:$EH13) --> Finds the position in the vector (single row) range CT13:EH13 of the last meter reading, i.e. the right-most cell having a number, which is the latest meter reading. Does this by comparing the values in that range with Excel's largest possible number. Because the values in the range are unordered and - especially - because no value will match that huge number, the MATCH function returns that position of the last cell that was evaluated - i.e. the last cell that has a number. Once that position is known, retrieve its value using INDEX. While you already understand this approach, I explain it here for the sake of other viewers.

Range of 11 prior months: OFFSET($CT13,0,[position of last reading]-12),,11) --> Defines the vector (single row) range containing the 11 months' meter reading immediately prior to the last meter reading. Does this using OFFSET, based on CT13 as the reference (starting) position, then setting the beginning of the range at the cell that is 12 prior (to the left) of the last meter reading and the ending point 11 cells further to the right of that beginning cell.
Note that ([position of last reading]-12) corresponds to the beginning (left-most cell) of the 11-month range.

11 month array: ISNUMBER([range of 11 prior months]) --> For that range of 11 cells, checks each cell to see if it has a corresponding meter reading. Does this via ISNUMBER evaluation that returns a boolean array such as {FALSE,TRUE,TRUE,...,TRUE} if the first cell is empty. Because this is an array formula, user must press CTL+SHIFT+ENTER to save it (or the larger formula that includes it). Although the results are held in a single cell, you can highlight (in the formula bar) this section within that larger formula and then press F9 to see the resulting array of boolean values (then press ESC to avoid replacing the array formula with its resulting values).

Position of first reading in array: MATCH(TRUE,[11 month array],0) --> Using MATCH, looking for an exact match for boolean TRUE, evaluates the array representing whether the prior 11 months have a meter reading. Returns the position of the first TRUE in the array (i.e. the first meter reading in the prior 11 month range).

Value of first reading: INDEX($CT13:$EH13,[position of first reading in array]+[position of beginning of 11 month range]). I think it would be easy to forget that last part. Because INDEX here references CT13, the very beginning of the range of (all) readings, you need to first advance to the position at the beginning of the 11 month range, then advance further to the position of the first meter reading within the ensuing 11 month range. Thus, the desired value is located at the index position that corresponds to the sum of those two subordinate positions.

SDL

 

[dwgnome]

Thank you for the great explanation, still a bit tricky on that second part. I'll try to use the F9 trick or use the evaluate formula button.

 

[SLARRA]

I took a whack at the Second Problem. This may be a horribly inefficient approach - hopefully, the real gurus will weigh in with better solutions for anything I have proposed.

I don't know if it's likely, but I assumed a maximum 12-month usage amount could occur multiple times for any given row, so I constructed formulas to identify the first and last occurrences. I was not able to account for missing readings, ala the First Problem you described in your original post. But, I'm guessing that won't be a problem here since the usage calculated for any 11-month or shorter span (calculated in lieu of a full 12-month span due to missing meter readings) should always be less than the usage calculated the following month for a full 12-month span. In other words, since you're interested in maximum 12-month usage here, it's probably safe to assume that the pair of readings that ultimately corresponds to the period of greatest usage will be spaced 12 months apart and not an irregular pair spaced only 11 months apart due to a missing meter reading.

You said your data (readings) lie in Columns H thru EC, and the formulas below evaluate just what's in Row 4 (copy down to evaluate Row 5 and beyond). All are array formulas, so use CTL+SHFT+ENTER to save them.

Max 12-mo usage: =MAX(S4:EH4-H4:DW4) <-- While readings start in Col H, Col S has the 12th month's readings
# times max occurred: =SUM((S4:EH4-H4:DW4=MAX(S4:EH4-H4:DW4))*1)
Location in row vector of end of 1st max usage period: =MATCH(MAX(S4:EH4-H4:DW4),S4:EH4-H4:DW4,0)+11 <-- Note that evaluation vector is 11 spots shorter than data vector (need the first 11 months' readings before the first annual usage calculation can be done)
Column corresponding to end of 1st max usage period: =SUBSTITUTE(ADDRESS(1,MATCH(MAX(S4:EH4-DW4),S4-EH4-H4-DW4,0)+18,4),"1","")
Meter reading at end of 1st max usage period =INDEX(S4:EH4,1,MATCH(MAX(S4:EH4-H4:DW4),S4:EH4-H4:DW4,0))
Location in row vector of end of latest max usage period: =MATCH(LOOKUP(2,1/(S4:EH4-H4:DW4=MAX(S4:EH4-H4:DW4)),S4:EH4),S4:EH4)+11
Column corresponding to end of latest max usage period: =SUBSTITUTE(ADDRESS(1,MATCH(LOOKUP(2,1/(S4:EH4-H4:DW4=MAX(S4:EH4-H4:DW4)),S4:EH4),S4:EH4)+18,4),"1","")
Meter reading at end of latest max usage period: =LOOKUP(2,1/(S4:EH4-H4:DW4=MAX(S4:EH4-H4:DW4)),S4:EH4)

Hope that does the trick!

SDL

 

[dwgnome]

SDL,

Thanks for taking a stab at the second part of my problem. I realized that my data starts at I6 not H4 for first row, so I adjusted your formulas. The first one worked, second one produced a number (37) which I am not sure is right, the third formula produced a number 123, the fourth resulted in #NA, the fifth resulted in the same number as the first, the sixth on produced #NA, the seventh produced "BJ" (presumably the column letters), the last produced the same number as the first.

So I am not sure what I am doing wrong.

 

[SLARRA]

There may be other problems, but I see where my failure to account for empty cells (no meter readings) or some non-numeric entry (your sample data shows asterisks instead of empty cells) would cause problems. I think the updated formulas below account for those scenarios. If other problems persist, we'll have to defer to a real expert.

Note that the 6th, 7th, & 8th formulas below should return the same answers as the 3rd, 4th, & 5th formulas if there is only one true maximum 12-month usage. I only included the latter group of formulas in anticipation of the possibility of there being two (or more) 12-month periods where the usage amount is the same AND it happens to be the maximum usage. If such ties are possible, it seems likely that you (or whoever) might be interested in knowing the last (most recent) time that it occurred (the MATCH method used for the formulas in the 3rd, 4th, & 5th group returns the first - i.e. earliest - occurrence).

Keeping in mind that all of these are array formulas, so they need to be saved in the cell by pressing CTL+SHFT+ENTER when you're done entering/copying them....

Max 12-mo usage: =MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""))
# times max occurred: =SUM((IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")=MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")))*1)
Location in row vector of end of 1st max usage period: =MATCH(MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")),IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""),0)+11
Column corresponding to end of 1st max usage period: =SUBSTITUTE(ADDRESS(1,MATCH(MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")),IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""),0)+19,4),"1","")
Meter reading at end of 1st max usage period =INDEX(T6:EH6,1,MATCH(MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")),IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""),0))
Location in row vector of end of latest max usage period: =MATCH(LOOKUP(2,1/(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")=MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""))),T6:EH6),T6:EH6)+11
Column corresponding to end of latest max usage period: =SUBSTITUTE(ADDRESS(1,MATCH(LOOKUP(2,1/(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")=MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""))),T6:EH6),T6:EH6)+19,4),"1","")
Meter reading at end of latest max usage period: =LOOKUP(2,1/(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,"")=MAX(IF(ISNUMBER(I6:DW6),T6:EH6-I6:DW6,""))),T6:EH6)

If you get #NA as results, double-check to ensure these were entered as array formulas (you should see curly brackets surrounding the formula when you view it in the formula bar).

Hope this gets you closer... I'm working at the very limit of my "expertise" here, so - for your sake - it would be nice for a real guru to weigh in!

SDL