Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing cells based on conditions in other cells
i need a function that takes cash values associated with dates and populates
a table based on fiscal quarters. ie. Company A Dec 23, 2008 250000 Company B January 2, 2008 450000 .... Q1 Q2 Q3 Q4 Company A 250000 Company B 450000 etc. however, there are multiple years to deal with and the fiscal years in question begin in march. i.e. Q1 is March 1-June 1. any help would be most appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing cells based on conditions in other cells
mirskman,
You have 3 columns of data (Company Name, Dollar Figure, Date). What you want is a table that sorts through all the info and comes out nice and clean and shows that dollar figure by Company per Quarter. What you need to get to that table is another table (either on the same sheet or a different sheet that tells us what each quarter responds to. It may look like the following: Q1 3/1/08 5/31/08 Q2 6/1/08 8/31/08 Q3 9/1/08 11/31/08 Q4 12/1/08 2/28/09 Now in the table you want, you have the companies listed on the left and the Quarters listed on the top. Since you have multiple years of data I would suggest labeling each quarter as 2008 Q1 vice just Q1. Either way the formula below will work. If the cell pivots at cell A1 then enter the following data in cell B2: =SUMPRODUCT((--($A2=CompanyNameData))*(DateData=INDEX(QuarterSta rtDates,MATCH(B$1,Quarters)))*(DateData<=INDEX(Qua rterEndDates,MATCH(B$1,Quarters))),(DollarFigureDa ta)) Where the following apply: CompanyNameData is the Name of the array that refers to the company column in the data given DollarFigureData is the Name of the array that refers to the dollar figure column in the data given DateData is the Name of the array that refers to the date column in the data given Quarters is the Name of the array that refers to the Quarter in the table above QuarterStartDates is the Name of the array that refers to the Quarter Start Date in the table above QuarterEndDates is the Name of the array that refers to the Quarter End Date in the table above The formula above is an array formula and must be inputted using Ctrl-Shft-Enter when done. Next Drag over to cover all Quarters, then drag down to cover all Company Names. This will add up the data as well in case there's more than one entry per company per quarter. You can also be creative when Naming your arrays so that it automatically updates when new data is inputted or when new quarters are added, but that's a different topic. Let me know if you don't understand anything on here. Yours, -- Brian "mirskman" wrote: i need a function that takes cash values associated with dates and populates a table based on fiscal quarters. ie. Company A Dec 23, 2008 250000 Company B January 2, 2008 450000 ... Q1 Q2 Q3 Q4 Company A 250000 Company B 450000 etc. however, there are multiple years to deal with and the fiscal years in question begin in march. i.e. Q1 is March 1-June 1. any help would be most appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cells based on conditions to another workbook | Excel Discussion (Misc queries) | |||
Copy cells based on conditions to another workbook | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Referencing cells text output if it meets specific conditions | Excel Worksheet Functions |