Prev: cannot change datasource of pivotchart
Next: Can I invert a matrix in Excel 2007?
From: KM on 27 Jan 2010 22:55
Hi,
Could someone please help me with this .

My spreadsheet has row data in weekly buckets . I would like these weekly
amounts to be summed to form the corressponding monthly total . However ,
some weeks are split across 2 months.

For e.g. w/c 28/06/10 has 3 days that fall into June and 4 days that fall
into July.
1) How do I get excel to add up all the weeks in June, and for this
particular week, instead of taking the full weekly value into June ,
calculate the value/7 * 3 days ?
2) How do I get the following month of July to also add in the value/7*4
days from this week , as it falls into July ?

w/c 07/06/10 14/06/10 21/06/10 28/06/10 05/07/10
12/07/10
Month Jun Jun Jun Jun
Jul Jul
Days/week 7 7 7 3 + 4
7 7

I'm not familiar with macros, but have used a few worksheet formulas . Can't
seem to find any vlookup/IF formula that will take all these conditions into
account .

Any help would be much appreciated.
Kind Rgds
KM


From: Herbert Seidenberg on 28 Jan 2010 22:01
Excel 2007 PivotTable, PivotChart
Convert week number (US) into date range,
then group by quarter, month, day.
http://www.mediafire.com/file/jtg5z41zmdm/01_28_10.xlsx
From: KM on 31 Jan 2010 17:15
Hi Herbert,

Thanks very much , will give it a try .

Kind Rgds
Kirthana

"Herbert Seidenberg" wrote:

> Excel 2007 PivotTable, PivotChart
> Convert week number (US) into date range,
> then group by quarter, month, day.
> http://www.mediafire.com/file/jtg5z41zmdm/01_28_10.xlsx
> .
>
 | 
Pages: 1
Prev: cannot change datasource of pivotchart
Next: Can I invert a matrix in Excel 2007?