| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Array
Can someone please help me out.
I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
Hi,
It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
Thanks, Mike.
A1 to L1 contains some values with A1 for Jan 09, B1 for Feb 09 etc etc therefore L1 would be for Dec 09. We are now in the month of July. I would like to compute YTD Actual and Rest of Year Forecast. I have to compute YTD and RoY every month. In the month of July, YTD would be sum of A1:F1 whereas RoY sum (G1:L1). I prefer not to use 12 cells to keep track of the 1s and 0s; instead I would like to use Name to store, say YTD = {1,1,1,1,1,1,0,0,0,0,0,0} and RoY = {0,0,0,0,0,0,1,1,1,1,1,1} which change every month. And I only need to change the contents of these 2 Names every month for the rest of the calculations to work. I researched exhaustively but cant seem to find a way to define an array in Name. Let me know if you need more clarifications. Thanks again. Rgds, On Jul 21, 5:17*pm, Mike H wrote: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 *multiplied by a row of 12 months data. Regards,- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
I guess what the OP wants but I can't do it (maybe it cannot be done):
Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an array of 12 element (constant 1s). He want the element automatically change value according to a certain month value, e.g. if this value is 6, then the returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0} Stefi €žMike H€ť ezt Ă*rta: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
Yes getting there. Thanks.
I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0} Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1. The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand. Expecting the result to be 6!!! Pls help! Rgds, On Jul 21, 5:36*pm, Stefi wrote: I guess what the OP wants but I can't do it (maybe it cannot be done): Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an array of 12 element (constant 1s). He want the element automatically change value according to a certain month value, e.g. if this value is 6, then the returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0} Stefi „Mike H” ezt írta: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 *multiplied by a row of 12 months data. Regards,- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
in Array constants ; (semicolon) separates Rows and , (comma) separates
columns, so at the moment you are multiplying 12 rows by 12 columns which yields 144 elements half of which are 1 and the other half zero so Sum=72 What you are looking for is ={1,1,1,1,1,1,0,0,0,0,0,0} Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "KC ppt Qns" wrote in message ... Yes getting there. Thanks. I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0} Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1. The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand. Expecting the result to be 6!!! Pls help! Rgds, On Jul 21, 5:36 pm, Stefi wrote: I guess what the OP wants but I can't do it (maybe it cannot be done): Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an array of 12 element (constant 1s). He want the element automatically change value according to a certain month value, e.g. if this value is 6, then the returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0} Stefi „Mike H” ezt írta: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards,- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
I got it all.
Thanks, Mike, Stefi and Charles. Have a good day! Cheers, On Jul 21, 6:26*pm, "Charles Williams" wrote: in Array constants ; (semicolon) separates Rows and , (comma) separates columns, so at the moment you are multiplying 12 rows by 12 columns which yields 144 elements half of which are 1 and the other half zero so Sum=72 What you are looking for is ={1,1,1,1,1,1,0,0,0,0,0,0} Charles ___________________________________ The Excel Calculation Sitehttp://www.decisionmodels.com "KC ppt Qns" wrote in ... Yes getting there. Thanks. I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0} Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1. The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand. Expecting the result to be 6!!! Pls help! Rgds, On Jul 21, 5:36 pm, Stefi wrote: I guess what the OP wants but I can't do it (maybe it cannot be done): Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an array of 12 element (constant 1s). He want the element automatically change value according to a certain month value, e.g. if this value is 6, then the returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0} Stefi „Mike H” ezt írta: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards,- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Array
If I want to compute YTD June....
As an alternative, to compute the sum of the first 6 cells in a range... =SUM(OFFSET(A1:L1,0,0,1,6)) The Last 6 cells... =SUM(OFFSET(A1:L1,0,11,1,-6)) = = = = = = HTH :) Dana DeLouis KC ppt Qns wrote: I got it all. Thanks, Mike, Stefi and Charles. Have a good day! Cheers, On Jul 21, 6:26 pm, "Charles Williams" wrote: in Array constants ; (semicolon) separates Rows and , (comma) separates columns, so at the moment you are multiplying 12 rows by 12 columns which yields 144 elements half of which are 1 and the other half zero so Sum=72 What you are looking for is ={1,1,1,1,1,1,0,0,0,0,0,0} Charles ___________________________________ The Excel Calculation Sitehttp://www.decisionmodels.com "KC ppt Qns" wrote in ... Yes getting there. Thanks. I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0} Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1. The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand. Expecting the result to be 6!!! Pls help! Rgds, On Jul 21, 5:36 pm, Stefi wrote: I guess what the OP wants but I can't do it (maybe it cannot be done): Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an array of 12 element (constant 1s). He want the element automatically change value according to a certain month value, e.g. if this value is 6, then the returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0} Stefi „Mike H” ezt írta: Hi, It's not very clear what you actually want to do but consider SUMPRODUCT. A formula such as the one below may help in which if the month in A1 to A10 is prior to August (<=7) then column B is summed. =SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10)) Mike "KC Excel Qns" wrote: Can someone please help me out. I would like to use a Name to contain 12 values, either 0s or 1s. If I want to compute YTD June 2009, this name will contain 1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I am thinking of using array. Likewise, if I want to compute Rest of Year 2009, this name will contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months data. Regards,- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named Ranges in Array Formulas | Excel Worksheet Functions | |||
| Vlookup - using a cell to reference a named array | Excel Worksheet Functions | |||
| Use named ranges in array formula | Excel Worksheet Functions | |||
| Named range into an array | Excel Worksheet Functions | |||
| Array to named range conversion... | Excel Discussion (Misc queries) | |||
Linear Mode
