Function Help!

[manni923]

Hi - I want to count based on a rolling data range and I can't seem to figure
out how to do it.

My data looks something like this:

Jan09 Feb09 Mar09 Apr09................ Jan10 Feb10 Mar10 Apr10
15 25 1 4 7 15
9 28

I want to count the numbers based on 12 months of data. So if Apr10 has a
number in it, I want to count from Apr10 all the way back 11 months through
May09.

If Apr10 was blank then I would like the formula to pick up from Mar10
(assuming it has a number in it) all the way back through Apr09

Is this possible?

 

[Bernie Deitrick]

Manni,

You could use an array formula (entered using Ctrl-Shift-Enter) like

=SUM(IF(COLUMN(B4:AE4)>=LARGE(IF(B4:AE4<>"",COLUMN(B4:AE4)),12),B4:AE4))

This formula will sum 12 non-blank values ending with the last filled cell
of row 4. (If there are other blanks to the left of the last filled cell,
they will be ignored, and a cell further to the left will be summed in.)

If you just want the last 12 columns - including blanks - as defined by the
last filled cell, then use (array entered)

=SUM(IF(COLUMN(B4:AE4)>=MAX(IF(B4:AE4<>"",COLUMN(B4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP

 

[manni923]

Hi Bernie,

Thanks for this information. I tried it out but I must be doing something
wrong.

I either get a #NUM! error message or a #VALUE error message. It wont sum
the numbers.

I changed the B4:AE4 to correspond to my data but it doesn't seem to pick it
up.

I even changed the way I set up the column headers so that all the blank
months (feb 2010 through dec 2010) were first. That put so all the
columns/row with data in them were together... January of 2010 was next to
the 2009 data which all has some value for each month.

Are you able to suggest something else for me to try or how I can fix this?

Thank you!!

 

[Gord Dibben]

Did you array-enter with Ctrl + Shift + Enter as Bernie posted?


Gord Dibben MS Excel MVP

 

[manni923]

yes, I tried that. It resulted in the #NUM! error message I received.

I guess I'm not sure what I'm doing wrong

 

[Bernie Deitrick]

Manni,

Post the formula that you actually tried to use.

HTH,
Bernie
MS Excel MVP

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+enter)

=SUM(INDIRECT(ADDRESS(4,MAX((A44=LOOKUP(999999,A44))*(COLUMN(A33)))-11)&":"&ADDRESS(4,MAX((A44=LOOKUP(999999,A44))*(COLUMN(A33))))))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com