| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
