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.
Thank you.
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.
