Calculate total metric based on sum of metric over specific time f

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.
0
Utf
1/27/2010 11:46:03 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
882 Views

Similar Articles

[PageSpeed] 31

I have a VBA solution if you wish to contact me
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"StaceyB" <StaceyB@discussions.microsoft.com> wrote in message 
news:54237C45-49DD-4FB2-BB2C-53EED43C73FC@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. 

0
Bernard
1/28/2010 7:45:37 PM
Reply:

Similar Artilces:

Outlook 2003 read time
How do you change the "read" time for Outlook 2003 messages so that they don't get marked as being read when you have clicked on them for just a couple of seconds? I had to change the setting in Outlook 2000 so it would wait 5 seconds before marking a message read and would like to do it again for 2003. Thank you! Right click on the reading pane options (right click on the dark gray border around the reading pane) and choose Reading Pane options. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Pr...

Update Cell Value Based on different Cell
Hi, new to the forum but I've been reading it for a couple of days now. Looking forward to talking and learning with everyone. I think I a better than average with Excel but this problem is driving me nuts! have a list of lottery numbers listed by date drawn (see below fo example) on one worksheet. On another sheet I have a count of how man times a number has been drawn. I would like to add a column to show th last date a number was drawn and have it update automatically when I ad a new drawing. However, I haven't been able to figure out how to ge the date to update automaticall...

CRM Time Clock Different Then System and Server Clock
Hi All, I have run into an odd situation whereas one of my users CRM lists a different time clock within CRM. When they create an entry in CRM there is a three hour difference between the time they enter it and what is actually recorded within CRM. For instance, the user will enter an update of information about a client and a conversation they had. They enter it at 11am and yet CRM shows that the entry was entered at 8am. Both the users local time clock and server are set for the correct time for Eastern Standard Time. This only appears to have happened with one user. No other ...

Formula for computing work time in Excel
Hi, For instance, A1 = 8:20, B1 = 16:30. I need to compute in C1 the following: B1 - A1 - 0.5 hour. What is the formula? If I use formula B1 - A1 - 0:30 I get invalid value. Thanks, -- Alex Vinokur email: alex DOT vinokur AT gmail DOT com http://mathforum.org/library/view/10978.html http://sourceforge.net/users/alexvn =B1-A1-TIME(0,30,0) -- HTH Bob Phillips "Alex Vinokur" <alexvn@big-foot.com> wrote in message news:uGyW$ZgvFHA.2008@TK2MSFTNGP10.phx.gbl... > Hi, > > For instance, A1 = 8:20, B1 = 16:30. > I need to compute in C1 the fo...

prevent discount below a specific profit percetage
Hi : how can i prevent cashier from salling below a minimum profit percetage or below a certain price. thank you You need to set a lower bound in the pricing tab of the item properties; I originally thought the lower bound price was the lowest price an item could be sold for but here's the help guide explanation - hope this helps Lower bound, Upper bound: These fields safeguard a cashier against selling an item below its lowest allowable price. The prices entered in these fields represent the recommended lowest and highest selling price of the item. Store Operations will display a ...

Calculate home equity in Money?
I have an asset account for my house. It is tied to a mortgage loan account. I also made a down payment from my checking account. How do I calculate the equity in the house using Money since it has appreciated and I have paid down some of the mortgage? Job #1: figure out what your house is worth. Nothing internal to Money will provide much help with that. Job #2, with a calculator, subtract sum of the current balance of all outstanding loans from the value determined in step 1. The remainder is the equity. "Taylor" <123@456.com> wrote in message news:Yp76g.184$Qq.73@...

Calculate height of listbox?
Is there a forumla to calculate the needed height of a listbox in order to contain a given number of lines of a given point size? Not without a bunch of GDI API work. The AutoSizeTextBox solution on my site shows you how. A far simpler solution, that is almost as accurate would be to use code/logic like: Assumes ListBox control named lbHeight Assumes TestBox control named Text11 Assumes CommandButton named CmdSize Enter a desired number of rows value in Text11 and then click on the CommandButton. Private Sub cmdSize_Click() On Error GoTo Err_cmdSize_Click Dim x As Integer ' conv...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

Macro to return cursor to specific column same row
Is there a simple way of returning the cursor to a specific column (say column "B") from any column, but keeping it in the same row? try cells(activecell.row,2).selec -- Kiera Born ignorant and still perfecting the ar ----------------------------------------------------------------------- Kieran's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=124 View this thread: http://www.excelforum.com/showthread.php?threadid=31864 Magic! Kieran - you're a champ! THANK YOU. "Kieran" wrote: > > try cells(activecell.row,2).select > &g...

calculation
Hi, I work with 3 large excel files (5000 KB). File A is my summary and is linked with the 2 others. I use a lot of reference cells and I discovered that some reference cells does not work. Some cells do not calculate automatically. I saved everything on the C drive in order to minimize the lenght of the formula but I still have problems. Any idea what else I could do? Thanks in advance. Can you share some examples of the cells that don't calculate? What is the formula in one of them? What's different between that cell and one that does recalculate? -- Regards, Tu...

OWA - Form Based Authentication
Hello All: Have OWA setup for form based authentication and works fine. What I have noticed is that it is not dropping a cookie on the clients machine for either private or public selection. I'd really like to do away with this altogether if possible but it looks like not an option. Why would the server no generate the appropriate cookie? Client machine is setup to accept cookies so it looks to be a server side issue. Any help apprecaited. ...

Time calculation #3
How can I calculate average time using hundredths of a second? For example: 1:43:23,88 is 1 hour, 43 minutes, 23 seconds and 88 hundredths of a second. If I want to divide this with, say 20, it's not so difficult to do by excluding the hundredths. But how is it done including it? (I want to use it to calculate average lap time running tracks). // Roger (Sorry for my poor english...) Roger, If you haven't done so, format it to include hundredths. Format - Cells - Number - Custom: hh:mm:ss,00 hh:mm:ss,000 -- Earl Kiosterud mvpearl omitthisword at verizon period net --------...

substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and selling price into cell H and get total in cell i. so it would have to substract cell F from cell H and put total into cell i. how do i do this? currency only Assuming your data is in Row 1, put this formula in I1 and press Enter.........be sure and type it just as shown, as the leading equal sign is what tells Excel that the following characters are a formula. =H1-F1 Vaya con Dios, Chuck, CABGx3 "vadarpug" <vadarpug@discussions.microsoft.com> wrote in message news:617768AC-D261-47AE-861A-E6BFD...

Autofill specific parameter
Hi, I am using excel2007 on XP SP3. My goal is to use the autofill feature on a certain parameter within my fomula. IE: A1='D:\[TEST1.xls]Sheet1'!$E$11 A2='D:\[TEST2.xls]Sheet1'!$E$11 A3='D:\[TEST3.xls]Sheet1'!$E$11 I only want to file name to change in seqeunce. Location of the file stays the same. It's this even possible? Any comments will be appreciated. In A1, paste : ="'D:\[TEST"&ROW()&".xls]Sheet1'!$E$11" Copy downwards. Select the formulae. Copy, Pastespecial values Replace : 'D:\[ with : ='D:\[ -- Regards.....

Total and query
I have a form frmreferb which has a sunbform called frmreferbdetails (Continues Form) on the subform I have a text box called quantity and a Priceperunit text box. I have a Total Text box with the control source set to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I would also like to do is calculate the total text box to give me a grand total on the frmreferb form. Does anyone have any ideas? The other thing I am trying to do is I have created a query called ReferbTotalsQuery this contains the quantity and total of each item. I need to calculate the total of each li...

Formula for counting specific cells which are greater than 1
I'm looking for a formula that will count specific cells (not a range - A17, A27, A37 and so on) that will have a certain value (above $1.00 to make it easy). So if the cell has a value of $1.00 or less it will not count in the total; If it is above $1.00 if will count. If A17 is above $1.00, A27 is $0.50 & A37 is $3.00 the count will be 2. ...

HOW TO: Plot a Team Name on a chart based on two values
Hi, I have the following table exmaple, what I like to do is to plot the team as where their values cross, e.g. for TEAM 01 where 5 and 60 cross on a graph having Revenue as the Y axis and Clients as the X axis. COLUMN A COLUMN B COLUMN C TEAMS CLIENTS REVENUE TEAM 01 5 60 TEAM 02 10 120 TEAM 03 15 180 TEAM 04 6 72 TEAM 05 12 144 TEAM 06 18 216 TEAM 07 7 84 TEAM 08 14 168 TEAM 09 21 252 TEAM 10 8 96 Any guidence, much appriciated. I've done this using Ron Bovey's XY Chart Labeler www.appspro.com "Kevin McCartney" <KevinMcCartney@discussions.microsoft.com> wrot...

I need a time sheet template that verifies time entered against sy
Try posting this again with your question in the message text instead of the subject. Your question got truncated. "Bob Powell" wrote: > ...

Why does my excel spreadsheet show ### in place of a sum?
For some reason, some autoSUM cells in my spreadsheet are showing up as "#" symbols even though they are correctly calculated in the function window... Any ideas? Try making the column wider. "S. Teichman" wrote: > For some reason, some autoSUM cells in my spreadsheet are showing up as "#" > symbols even though they are correctly calculated in the function window... > Any ideas? Typically the # characters indicate that the column is not wide enough to display the result. Try making the column wider. -- Cordially, Chip Pearson Microsoft MVP - E...

Changing the calculation order
Can the order that Excel recalculates be changed to start fro the lower right? Excel automatically adjusts its final calculation order depending on the dependency chain in such a way as to get the correct result regardless of formula sequence. Why would you want to change this? Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "A Process Modeler" <A Process Modeler@discussions.microsoft.com> wrote in message news:4D17FF7C-D763-4FAB-980C-463DB8A75FCA@microsoft.com... > Can the order that Excel recalculates be changed to ...

Using Tab key to jump to specific cell
I've created a 'fillable form' and somehow (magically) when the tab key is pressed in certain cells, the cursor jumps to the next appropriate cell to be filled (Example I filled in cell B1 and the next fillable cell is D4. If I hit the tab key after typing in B1, the cursor will jump to D4). But this does not happen in all cells or at the appropriate places. I don't know how I managed to get it to do what it does already. Is there is a way to fine-tune it? When you protect a sheet, tabbing will cause the active cell to jump between the unlocked (aka unprotect...

receiving messages multiple times
Outlook 2002, set in POP3 mode, set to leave a copy of messages on the (offsite) server. Whenever mail is received at the server, it is forwarded properly to this user, but then in 5 minutes or so when Outlook checks the server again (as it has always done in the past) another copy of the message is sent. This will continue until a NEW message arrives at the server, at which point that new message will be repeatedly sent. There is only one copy of the email on the server, and another user who monitors this account using Outlook Express properly receives a single instance of the messa...

Send email from SPECIFIC account only
I'm using Outlook 2002. I have three accounts: 1. myname@wherever.net -- used for personal emails 2. my.full.name@wherever.net -- used for business emails 3. name@school.edu -- still used for school emails (will be phased out) Accounts 1 & 2 use the same ISP. They are both alias addresses for <my.mungedname@wherever.net>. Account 3 is an IMAP account from a different ISP. I have rules to sort mail to separate folders: MYNAME folder and MY.FULL.NAME folder. They work. What I want is this: I want to work from the MYNAME folder, create a new email, and have it sent...

Activating bold function takes time
I have a PC which is having Office XP installed in it. When i try to make a text in a cell bold the first time in any worksheet, it takes aprroximately 4-5 mins, but the very next moment if i want to make another cell bold, it happens as normal. can u help with the reason why it so happens & is there any solution for the same. I also tried reinstalling Office (different version) twice after removing the previous installed version but the problem is still persisting. I think I've read posts that say that when this kind of thing happens, it's usually because the current printe...

Can i replay logs with eseutil multiple times on the same EDB file?
hi all, I was wondering if anyone was ever able to replay logs through eseutil multiple times on the same database. I tried issueing the command "eseutil /r E00 /D" once on the original set of logs and it succeeded. When I added some more logs to the same folder and issued the same command, it appears that no new logs are replayed to the database. However, if I only call ESEUTIL once on the comprehensive set of logs (the original set of logs plus the additional ones), then it works fine and all the logs are incorporated. It seems that ESEUTIL will only allow you to replay logs to ...