Function Help!
 

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?

Function Help!
 

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(B 4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP



"manni923" wrote in message
...
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?

Function Help!
 

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!!



"Bernie Deitrick" wrote:

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(B 4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP



"manni923" wrote in message
...
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?


.

Function Help!
 

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


Gord Dibben MS Excel MVP

On Fri, 29 Jan 2010 12:30:10 -0800, manni923
wrote:

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!!



"Bernie Deitrick" wrote:

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(B 4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP



"manni923" wrote in message
...
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?


.

Function Help!
 

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

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


"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Fri, 29 Jan 2010 12:30:10 -0800, manni923
wrote:

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!!



"Bernie Deitrick" wrote:

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(B 4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP



"manni923" wrote in message
...
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?


.


.

Function Help!
 

Manni,

Post the formula that you actually tried to use.

HTH,
Bernie
MS Excel MVP



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

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


"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Fri, 29 Jan 2010 12:30:10 -0800, manni923
wrote:

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!!



"Bernie Deitrick" wrote:

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(B 4:AE4)))-11,B4:AE4))

HTH,
Bernie
MS Excel MVP



"manni923" wrote in message
...
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?


.


.

Function Help!
 

Hi,

Try this array formula (Ctrl+Shift+enter)

=SUM(INDIRECT(ADDRESS(4,MAX((A4:P4=LOOKUP(999999,A 4:P4))*(COLUMN(A3:P3)))-11)&":"&ADDRESS(4,MAX((A4:P4=LOOKUP(999999,A4:P4)) *(COLUMN(A3:P3))))))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"manni923" wrote in message
...
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?