Prev: HOW CAN I IMPLEMENT SUMIF(D1:D6,">AVERAGE(D1:D6)")
Next: cannot change datasource of pivotchart
From: StaceyB on 27 Jan 2010 18:46
I am trying to calculate the sum of specific metrics as they occur over a
certain time period.

You can find the spreadsheet I'm using here:
http://www.staceyboyd.com/excel/example.zip (I had to put it in a zip file,
otherwise the browser didn't seem to want to access it.)

Basically, in the Total section (starting N5), I'm trying to sum that
particular metric (in this example Impression estimates) that were posted
during the month that appears to the in the M5-M11 cells).

The first place you could actually even see a calculated summary would be N7
for Jan-2010.
So here is what I want to accomplish. It should sum the Estimated
Impressions that occurred in January. So this example would have 4500
impressions for the first records, plus a portion of the 1598 impressions
from the second record. The portion of the second record should be based on
the percentage of days that fell within January for the whole campaign. In
this example, 17 or a total 25 days fell within January. So of the 1598
impressions for the 2nd campaign, 1086.64 can be attributed to January (if
evenly distributed across the time period). The number of days for a given
month can be seen in cells B20 - I23 for these 4 existing records.

I hope I haven't made this too confusing, and if there is an easier method
let me know, but I still haven't been able to get my sum. I'm assuming I'll
need an array of some sort?

I'll need to populate all cells appearing within the total section, but once
I have one, the rest should be pulled the same way.

So in summary, for cell N7, I should get the value 5,586.64 (i.e.
4500+1086.64).

Can you let me know how I can calculate this automatically?

Your help is GREATLY appreciated.

NOTE: The months in cells M5-M11 are calculated based on B17-H17.
From: Bernard Liengme on 28 Jan 2010 14:45
I have a VBA solution if you wish to contact me
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"StaceyB" <StaceyB(a)discussions.microsoft.com> wrote in message
news:54237C45-49DD-4FB2-BB2C-53EED43C73FC(a)microsoft.com...
> I am trying to calculate the sum of specific metrics as they occur over a
> certain time period.
>
> You can find the spreadsheet I'm using here:
> http://www.staceyboyd.com/excel/example.zip (I had to put it in a zip
> file,
> otherwise the browser didn't seem to want to access it.)
>
> Basically, in the Total section (starting N5), I'm trying to sum that
> particular metric (in this example Impression estimates) that were posted
> during the month that appears to the in the M5-M11 cells).
>
> The first place you could actually even see a calculated summary would be
> N7
> for Jan-2010.
> So here is what I want to accomplish. It should sum the Estimated
> Impressions that occurred in January. So this example would have 4500
> impressions for the first records, plus a portion of the 1598 impressions
> from the second record. The portion of the second record should be based
> on
> the percentage of days that fell within January for the whole campaign.
> In
> this example, 17 or a total 25 days fell within January. So of the 1598
> impressions for the 2nd campaign, 1086.64 can be attributed to January (if
> evenly distributed across the time period). The number of days for a given
> month can be seen in cells B20 - I23 for these 4 existing records.
>
> I hope I haven't made this too confusing, and if there is an easier method
> let me know, but I still haven't been able to get my sum. I'm assuming
> I'll
> need an array of some sort?
>
> I'll need to populate all cells appearing within the total section, but
> once
> I have one, the rest should be pulled the same way.
>
> So in summary, for cell N7, I should get the value 5,586.64 (i.e.
> 4500+1086.64).
>
> Can you let me know how I can calculate this automatically?
>
> Your help is GREATLY appreciated.
>
> NOTE: The months in cells M5-M11 are calculated based on B17-H17.

 | 
Pages: 1
Prev: HOW CAN I IMPLEMENT SUMIF(D1:D6,">AVERAGE(D1:D6)")
Next: cannot change datasource of pivotchart