Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling up a snapshot of data by month
I need to know a way to capture a snapshot of data as of the date entered
into column B each month. The problem is that as items are "Closed", the report for the previous month's data changes. Here's my report spreadsheet: A: Month B: Date Checked C: Total Submitted to date D: # Completed at check (date that is entered into column B) E: % Completed at check These are rolling totals based on what is entered in the "Data Entry" sheet, but I do not want them dynamic. I want January's data to stay January's data, even if something is "Closed" in February (it would then count in February's totals). Formulas are as follows: C: =IF(ISBLANK(B3),"",COUNTIF('Data Entry'!$B$2:$B$2000,"<="&(B3))) D: =IF(ISBLANK(B3),"",SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000<=(B3)))) E: =IF(ISBLANK(B3),"",(D3/C3)) Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling up a snapshot of data by month
Mercdoodle wrote:
I need to know a way to capture a snapshot of data as of the date entered into column B each month. The problem is that as items are "Closed", the report for the previous month's data changes. Here's my report spreadsheet: A: Month B: Date Checked C: Total Submitted to date D: # Completed at check (date that is entered into column B) E: % Completed at check These are rolling totals based on what is entered in the "Data Entry" sheet, but I do not want them dynamic. I want January's data to stay January's data, even if something is "Closed" in February (it would then count in February's totals). Formulas are as follows: C: =IF(ISBLANK(B3),"",COUNTIF('Data Entry'!$B$2:$B$2000,"<="&(B3))) D: =IF(ISBLANK(B3),"",SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000<=(B3)))) E: =IF(ISBLANK(B3),"",(D3/C3)) Any ideas? What you really need is the calendar day (or month) of activity in your source data. Then you can get the correct calendar month summary as you seek. If that is not an option, you need to "lock down" each month, e.g., by replacing the formulas with values (copy/paste special|values). But unless you know exactly what the data looked like at month-end, you might not be able to recreate prior month totals now. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling 12 month data charts | Charts and Charting in Excel | |||
Rolling 13 Month Charts | Charts and Charting in Excel | |||
rolling 12 month average | Excel Worksheet Functions | |||
How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) | |||
3 month rolling chart | Charts and Charting in Excel |