Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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")),I ncome!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. Thanks for anyones help in advance, I appreciate your time!! - Ian -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Dynamically determining when a month ends
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" wrote in message ... 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")),I ncome!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. Thanks for anyones help in advance, I appreciate your time!! - Ian -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Dynamically determining when a month ends
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...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Dynamically determining when a month ends
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")),I ncome!C2:C700) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Moomancow3k" wrote in message ... 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...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) | |||
4 and 5 week months | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |