LTM

[Rick1427]

HI there,
Is there an easy way to calculate Last Twelve Months' number if you have leading blanks and lagging blanks in a range of say 100 columns? I've tried the following

=SUM(OFFSET(O9,0,COUNT(P9:DS9),1,-MIN(COUNT(P9:DS9),12)))

Where column O is the starting point,
Count P9:DS9 is the number of months of revenue from a client (could be 1 to 100 months of revenue in a 100 month set),
and the -min part is simply saying take either 12 months or the number of months there, (e.g. 3)

The problem. is my starting point needs to take into account the number of leading blanks but I'm not sure out to add +Countblanks(P9:P___) where ___ needs to be dynamic.

I thought about a way a lookup can find where blanks turn to zeros but I'd need 200 helper cells. Any other thoughts?

Thanks!

-Rick

 

[KRice]

It's not clear to me whether you want to sum the cells beginning closer to the P column or the DS column. I'm assuming the P column end. See if this is close to what you want. This counts how many nonblank cells are in the full range and then creates a sequenced array from 1 up to the count, not exceeding 12. Let's call that number of cells m. Starting at column P, the first m columns that have numeric values are summed. If you want the first m columns starting at column DS and working back toward column P, change the "15" to "14" in the formula. Since you are using 365, you probably will not have to enter this with Control-Shift-Enter (should be natively recognized).

Book2
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
7	sum	25
8	column #	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
9	values		1	1	3		3	1	2		3	3		2	1		1		4
Sheet1

Cell Formulas
Range	Formula
P7	P7	=SUM(INDEX($P9:$DS9,N(IF(1,AGGREGATE(15,6,(COLUMN($P9:$DS9)-COLUMN($O9))/($P9:$DS9<>""),ROW(INDIRECT(1&":"&MIN(COUNT($P9:$DS9),12)))),0))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Rick1427]

HI there,
Is there an easy way to calculate Last Twelve Months' number if you have leading blanks and lagging blanks in a range of say 100 columns? I've tried the following

=SUM(OFFSET(O9,0,COUNT(P9:DS9),1,-MIN(COUNT(P9:DS9),12)))

Where column O is the starting point,
Count P9:DS9 is the number of months of revenue from a client (could be 1 to 100 months of revenue in a 100 month set),
and the -min part is simply saying take either 12 months or the number of months there, (e.g. 3)

The problem. is my starting point needs to take into account the number of leading blanks but I'm not sure out to add +Countblanks(P9:P___) where ___ needs to be dynamic.

I thought about a way a lookup can find where blanks turn to zeros but I'd need 200 helper cells. Any other thoughts?

Thanks!

-Rick

...leading blanks meaning many cells with blank values, then any number of populated cells, then any number of blank cells again. Thanks!

 

[Rick1427]

It's not clear to me whether you want to sum the cells beginning closer to the P column or the DS column. I'm assuming the P column end. See if this is close to what you want. This counts how many nonblank cells are in the full range and then creates a sequenced array from 1 up to the count, not exceeding 12. Let's call that number of cells m. Starting at column P, the first m columns that have numeric values are summed. If you want the first m columns starting at column DS and working back toward column P, change the "15" to "14" in the formula. Since you are using 365, you probably will not have to enter this with Control-Shift-Enter (should be natively recognized).

Book2
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
7	sum	25
8	column #	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
9	values		1	1	3		3	1	2		3	3		2	1		1		4
Sheet1

Cell Formulas
Range	Formula
P7	P7	=SUM(INDEX($P9:$DS9,N(IF(1,AGGREGATE(15,6,(COLUMN($P9:$DS9)-COLUMN($O9))/($P9:$DS9<>""),ROW(INDIRECT(1&":"&MIN(COUNT($P9:$DS9),12)))),0))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Wow thanks very much. I'll give it a try.