#### Dynamically determining when a month ends

```Hello all,

Ok, I have a simple excel spreadsheet, where I keep track of my
spending on one tab, my income in another and my summary in a thrid.

My problem happens when I try to dynamically calc. avg's for months in
the summary page.  For instance, if I want to see how much money I
spent on average per month, or even per day, I draw from the data
entered into the spending page.

Now, the spending page is set up with the following columns:
Date:: Description::  Amount

The problem with this is that I can't predefine a max row # for each
month, since I might have 80 entries in spending in Jan., and only 10
in Feb.

There has to be a way to do this.  I've been playing around with it,
and this is as far as I got...

=SUMIF(Income!A2:A700,=AND(">=38718","<=38748")),Income!C2:C700)

That would be for the month of Jan.  (I have calc. the numbers from the
number of days that 1/1/06 and 1/31/06 are from Jan. 1, 1990)

I think my problem lies within my "AND" statement.  Is the syntax
correct?  Is the semantics correct??  Is there an easier way to do
this??

If my explaination was unclear in any way, just ask me to clairfy, and
I'd be glad to try and do that.

- Ian

--
Moomancow3k
------------------------------------------------------------------------
Moomancow3k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30339

```
 0
1/11/2006 4:01:21 AM
excel.newusers 15348 articles. 2 followers.

3 Replies
728 Views

Similar Articles

[PageSpeed] 1

```One way is to use SUMPRODUCT

In say, Sheet1:

Put the Start date in A1: 01-Jan-06, End date in B1: 31-Jan-06

Then put in say, C1:
=IF(OR(A1="",B1=""),"",SUMPRODUCT((Income!\$A\$2:\$A\$700>=A1)*(Income!\$A\$2:\$A\$7
00<=B1),Income!\$C\$2:\$C\$700))

C1 can be copied down to return correspondingly
for other pairs of start/end dates in A2:B2, A3:B3, etc
--
Max, Singapore GMT+8, xl97
Samples archive at: http://savefile.com/projects/236895
xdemechanik
--
"Moomancow3k" <Moomancow3k.21gcmm_1136952603.8496@excelforum-nospam.com>
wrote in message
news:Moomancow3k.21gcmm_1136952603.8496@excelforum-nospam.com...
>
> Hello all,
>
> Ok, I have a simple excel spreadsheet, where I keep track of my
> spending on one tab, my income in another and my summary in a thrid.
>
> My problem happens when I try to dynamically calc. avg's for months in
> the summary page.  For instance, if I want to see how much money I
> spent on average per month, or even per day, I draw from the data
> entered into the spending page.
>
> Now, the spending page is set up with the following columns:
> Date:: Description::  Amount
>
> The problem with this is that I can't predefine a max row # for each
> month, since I might have 80 entries in spending in Jan., and only 10
> in Feb.
>
> There has to be a way to do this.  I've been playing around with it,
> and this is as far as I got...
>
> =SUMIF(Income!A2:A700,=AND(">=38718","<=38748")),Income!C2:C700)
>
> That would be for the month of Jan.  (I have calc. the numbers from the
> number of days that 1/1/06 and 1/31/06 are from Jan. 1, 1990)
>
> I think my problem lies within my "AND" statement.  Is the syntax
> correct?  Is the semantics correct??  Is there an easier way to do
> this??
>
> If my explaination was unclear in any way, just ask me to clairfy, and
> I'd be glad to try and do that.
>
>
> - Ian
>
>
> --
> Moomancow3k
> ------------------------------------------------------------------------
> Moomancow3k's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30339
>

```
 0
demechanik (4694)
1/11/2006 5:07:35 AM
```When I tried to enter your code into a new spreadsheet file, I put the
data you said to into sheet1, as well as the line of code...

I renamed sheet2 "Income", and entered some data...

However, I get an "Invalid Cell Reference Error" on Sheet1 with your
forumula...

Any ideas?

I copied and pasted your line for Sheet1!C1 exactly.

Thanks!

--
Moomancow3k
------------------------------------------------------------------------
Moomancow3k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30339

```
 0
1/11/2006 6:52:50 PM
```I'm not sure what went wrong for you,
but here's a sample implementation:
http://cjoint.com/?blxSiW6QdK
Moomancow3k_newusers.xls

I've tried to structure it to follow closely the sheetname, range
references, etc you provided earlier in your posted formula:
> =SUMIF(Income!A2:A700,=AND(">=38718","<=38748")),Income!C2:C700)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moomancow3k" <Moomancow3k.21hhln_1137005720.6296@excelforum-nospam.com>
wrote in message
news:Moomancow3k.21hhln_1137005720.6296@excelforum-nospam.com...
>
> When I tried to enter your code into a new spreadsheet file, I put the
> data you said to into sheet1, as well as the line of code...
>
> I renamed sheet2 "Income", and entered some data...
>
> However, I get an "Invalid Cell Reference Error" on Sheet1 with your
> forumula...
>
>
> Any ideas?
>
> I copied and pasted your line for Sheet1!C1 exactly.
>
> Thanks!
>
>
> --
> Moomancow3k
> ------------------------------------------------------------------------
> Moomancow3k's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30339
>

```
 0
demechanik (4694)
1/11/2006 10:53:37 PM

Similar Artilces:

Data markers in a stacked chart will not sit side by side by month #2
How can I get the monthly columns (ie: July Actual & July Budget) in a stacked chart to sit side by side with a space before the next month (August Actual & August Budget) and so on for each month. ...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

Month view: scroll to see more?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: Exchange Hello-- At MIT we have recently migrated to Exchange, which seems to be a setback in myriad ways. Does anyone know how to scroll in calendar Month View to see all items on a single day? I need to stay in Month View but have the ability to see all items on each day. I don't want to move to day view. Thanks! ...

Posting Date (POSTEDDT) in the front end
Customer wants to see the Posting Date (POSTEDDT) in SOP30200. This field is using the computer's date and customer wants to see when exactly the sales document was posted. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

get records from before 6 months ago
Hello everyone, I'm trying to get records BEFORE 6 months ago based on 2 date feilds. Here's the script: if object_id('tempdb.dbo.#hha') > 0 drop table #hha select * into #hha from temp.dbo.op with (nolock) where (Date1 < DATEADD(MONTH, -6, GETDATE()) and Date1 <> '1900-01-01 00:00:00') or (Date2 < DATEADD(MONTH, -6, GETDATE()) and Date2 <> '1900-01-01 00:00:00') *After executing the script and looking the data in the temp table I don't think the logic is correct. What do you guys think? Any help will be gre...

Round up price to end .99
I want to raise prices by 5% and then round the resulting number to end in xxx.99. For example, if the result is \$15.48, I want to round that to \$ 15.99. Any help? It doesn't sound like you really want to round up--it sounds like you just want to make sure it ends with 99 cents. Maybe... =int(a1*1.05)+.99 Chip wrote: > > I want to raise prices by 5% and then round the resulting number to end in > xxx.99. For example, if the result is \$15.48, I want to round that to \$ > 15.99. Any help? -- Dave Peterson And you've got more replies at your other post, too. ...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...

Dynamic Range
Hi All, I have the following two dynamic ranges =OFFSET(PBR!\$A\$1,0,0,COUNTA(PBR!\$A:\$A),1) =OFFSET(PBR!\$A\$1,0,0,COUNTA(PBR!\$A:\$A),2) Note: there will be two ranges namecount & namelist The formula which would be using the above range is =IF(MAX(namecount)<ROW(1:1),"",VLOOKUP(ROW(1:1),namelist,2)) Big question!!!, can I join these ranges together in to one statement. if so than how ??? Thansks, Rashid Hi! If I follow what you want: Named range: List Refers to: =OFFSET(PBR!\$A\$1:\$B\$1,,,COUNTA(PBR!\$A:\$A)) Then: =IF(MAX(INDEX(List,,1))<ROW(1:1),"",VLOOKUP(R...

Creating a dynamic chart
I posted here a few days ago and was given some ideas on how to do this. However, I seem lost. I have a main worksheet which is updated from several worksheets by using code. On this worksheet is approximately 15-20 columns something like this. trimester year RW RW% WIW WIW% etc 1st 2001 80 89% 79 75% 2nd 2001 90 89% 84 87% 3rd 2001 87 88% 92 89% 1st 2002 88 88% 90 88% etc etc What I would like to do is create one chart and by selecting the title of the column from either a combo box or by using a bu...

Determining if user has "write/save" permissions
Hello All! I am using Excel 2000 and I have a question. I have a program (Delphi) which makes a call that executes an excel macro. The macro does nothing more than change the value of the "zooming/scaling" value from whatever it is to 100%. For some users, who don't have WRITE/SAVE access to the directory where the .xls files live, the newly modified .xls file gets saved into the "My Documents" folder instead of the network drive where the .xls originally was opened. My question is, is there a way to determine if a user has the "proper rights" for...

Dynamics Workflow windows in Dynamics
Hi there I have installed Dynamics Workflow for Dynamics version 10 successfully. I have created a simple Purchase Order workflow. I have clicked the Activate Workflow button in this company. However when the users capture a Purchase Order it does not have the additional workflow compnonents on the Purchase Order window e.g. Submit etc. I have logged out and back in again. What am I missing? Regards Robin The workflow functionality in GP 10 requires the deployment of MOSS 2007 (Enterprise Edition). Not a trivial thing. "Robinv" wrote: > Hi there > > I have ...

Dynamic Information in Templates
I would like to create a template with textboxes whose contents would be filled with the Personal Information of the user that is creating the document. If I use Insert->Personal Information in the template, the same text stays no matter who opens. Does anyone know how to make it change for each user? ...

dynamic charts
Hi: I have a series of monthly reports within excel. Each of those reports has a raw data tab. The raw data goes from left to right and each column represents an additional month. The rows represent different metrics that are tracked over time. How can I create my charts so that when I add an additional months worth of data (ex: adding a column to the raw data table) the charts dynamically update with a rolling 13 month trend? I have seen other examples where the raw data is setup differently and can't format my data that way. Specifically, people had the months in each row and the ...

CRM Dynamics Domain
I have in my posssion the following domain: www.crmdynamics.com I am ready to sell it. Make your best offer at : crmdynamics@gmail.com . I bid \$0.01. -- Matt Parks MVP - Microsoft CRM "CRM Chief" <CRMChief@discussions.microsoft.com> wrote in message news:0F150CC7-6A73-4B4B-BB76-3DA5B7282949@microsoft.com... I have in my posssion the following domain: www.crmdynamics.com I am ready to sell it. Make your best offer at : crmdynamics@gmail.com . ...

Template for 12 month P&L UK please!!
Looking for an Excell spreadsheet giving a 12 month P&L projection for the UK please. Format Sales, Cost of Sales, Expenses and profit before and after tax. ...

how to convert 12/10/1970 to years/months/days
can any one help me to covert 12/10/1970 to years/months/days Hi With date in A1 =Year(A1) =Month(A1) =Day(A1) -- Regards Roger Govier "doctor who golf" <doctorwhogolf@discussions.microsoft.com> wrote in message news:0C009ACE-2631-4770-9CCE-20308E198D58@microsoft.com... > can any one help me to covert 12/10/1970 to years/months/days Maybe you mean =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days" -- HTH Bob Phillips (replac...