I am combining data in several columns into one singlecolumn using this formula:
=OFFSET(AccRange,MOD(ROW()-ROW(ColumnData),ROWS(AccRange)),TRUNC((ROW()-ROW(ColumnData))/ROWS(AccRange),0),1,1)
I’m using named ranges: AccRange = columns I’m combining; ColumnData = column where I’m placingall my data; Period = month number
However, I need to add a few elements to the formula, and thatis where I am not sure how:
(a) ignore blanks - I thought I could add=SMALL(AccRange,ROW())
(b) only look at rows where the named range Period = $F$4
(c) if any number in the named range AccRange is negative,make it positive
(d) round numbers in named range AccRange to 2 decimals
Any advise?
=OFFSET(AccRange,MOD(ROW()-ROW(ColumnData),ROWS(AccRange)),TRUNC((ROW()-ROW(ColumnData))/ROWS(AccRange),0),1,1)
I’m using named ranges: AccRange = columns I’m combining; ColumnData = column where I’m placingall my data; Period = month number
However, I need to add a few elements to the formula, and thatis where I am not sure how:
(a) ignore blanks - I thought I could add=SMALL(AccRange,ROW())
(b) only look at rows where the named range Period = $F$4
(c) if any number in the named range AccRange is negative,make it positive
(d) round numbers in named range AccRange to 2 decimals
Any advise?