Formula Help

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
173
hello
not sure if this is possible, but i have a list of unique identifiers in column A and columns B-M refer to Periods 01-12(months). each unique identifier may not have data start on the same period, meaning one site may have first data in period 01 and identifier 2 may have it in period 06. i wanted to see if there was a way to put a formula in that would allow me to compare data for each site in 3 months increments. i don't know if this makes sense but this is how it would look below. in the below example i am assuming there is only 6 periods because of table limitations. not sure if this makes sense but hopefully this diagram makes sense

Biz SitePeriod 01Period 02Period 03Period 04Period 05Period 63 month difference6 month difference9 month difference
ABC2.004.006.008.0010.004.00
BCD1.002.003.004.005.006.002.005.00
EFG5.007.009.004.00
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. This should do it for you (I'm sure there may be simpler answers, but it does the job):
Book3 (version 1).xlsb
ABCDEFGHIJ
1Biz SitePeriod 01Period 02Period 03Period 04Period 05Period 63 month difference6 month difference9 month difference
2ABC2468104  
3BCD12345625 
4EFG5794  
Sheet2
Cell Formulas
RangeFormula
H2:H4H2=IF(ISERROR(INDEX(B2:G2,1,2+MATCH(FALSE,ISBLANK(B2:G2),0))),"",INDEX(B2:G2,1,2+MATCH(FALSE,ISBLANK(B2:G2),0))-INDEX(B2:G2,1,MATCH(FALSE,ISBLANK(B2:G2),0)))
I2:I4I2=IF(ISERROR(INDEX(B2:G2,1,5+MATCH(FALSE,ISBLANK(B2:G2),0))),"",INDEX(B2:G2,1,5+MATCH(FALSE,ISBLANK(B2:G2),0))-INDEX(B2:G2,1,MATCH(FALSE,ISBLANK(B2:G2),0)))
J2:J4J2=IF(ISERROR(INDEX(B2:G2,1,8+MATCH(FALSE,ISBLANK(B2:G2),0))),"",INDEX(B2:G2,1,8+MATCH(FALSE,ISBLANK(B2:G2),0))-INDEX(B2:G2,1,MATCH(FALSE,ISBLANK(B2:G2),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,940
Messages
5,545,112
Members
410,659
Latest member
theloudbloke
Top