Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month,Previous Month and Month previous to that
Hi
I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows CURRENT MONTH EXPENSES D2= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( )),Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)) MONTH[-1] EXPENSES D3= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( ))-1,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2 MONTH[-2] EXPENSES D4=ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(N OW())-2,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2-D3 This works well till December. But when it comes to January and the Month number becomes 1 again, this formula will not work for calculating previous months' cost. I would be immensely grateful if I could get an idea on how to trap the previous months' expenses when the current month becomes January Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi,
Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San: CURRENT MONTH EXPENSES D2= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( )),Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)) MONTH[-1] EXPENSES D3= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( ))-1,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2 MONTH[-2] EXPENSES D4=ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(N OW())-2,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2-D3 try it with a real date: =MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1)) =MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),0)) =MONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi again,
Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San: I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows if your date is in column B and the values in D:Y you can also use: =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY()))*Cost!D1:Y1000) =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-1)*Cost!D1:Y1000) =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-2)*Cost!D1:Y1000) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
Thanks Claus. Will try the same and revert back
San On Saturday, August 9, 2014 11:55:56 PM UTC+5:30, Claus Busch wrote: Hi again, Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San: I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows if your date is in column B and the values in D:Y you can also use: =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY()))*Cost!D1:Y1000) =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-1)*Cost!D1:Y1000) =SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-2)*Cost!D1:Y1000) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Here's a sample of how I do this...
https://app.box.com/s/23yqum8auvzx17h04u4f Look for the file "IncomeExpense.xlt"! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi,
Am Sat, 9 Aug 2014 11:36:03 -0700 (PDT) schrieb San: Will try the same and revert back you can also use: =MONTH(TODAY()) =MONTH(EDATE(TODAY(),-1)) =MONTH(EDATE(TODAY(),-2)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
On Sunday, August 10, 2014 8:29:34 PM UTC+5:30, Claus Busch wrote:
Hi, Am Sat, 9 Aug 2014 11:36:03 -0700 (PDT) schrieb San: Will try the same and revert back you can also use: =MONTH(TODAY()) =MONTH(EDATE(TODAY(),-1)) =MONTH(EDATE(TODAY(),-2)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Thanks again Claus. This is far simpler than the first one and it is working. BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January. Any help on that ? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi,
Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San: BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January. try: =WEEKNUM(TODAY(),2) =WEEKNUM(TODAY()-7,2) =WEEKNUM(TODAY()-14,2) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
On Monday, August 11, 2014 11:49:10 AM UTC+5:30, Claus Busch wrote:
Hi, Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San: BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January. try: =WEEKNUM(TODAY(),2) =WEEKNUM(TODAY()-7,2) =WEEKNUM(TODAY()-14,2) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Great!.. I should have thought in that line. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
On Monday, August 11, 2014 12:08:24 PM UTC+5:30, San wrote:
On Monday, August 11, 2014 11:49:10 AM UTC+5:30, Claus Busch wrote: Hi, Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San: BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January. try: =WEEKNUM(TODAY(),2) =WEEKNUM(TODAY()-7,2) =WEEKNUM(TODAY()-14,2) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Great!.. I should have thought in that line. Thanks If I have to sum on a Financial Year-wise basis (i.e. from April - Mar of next year), I have inserted a column where the Financial Year is calculated as follows: E2=IF(OR(C2=1,C2=2,C2=3),YEAR(A2)-1,YEAR(A2)) [where C2 = Month serial no.of A2) Now for Financial Year-wise tabulation, say for the last Financial Year, the formula in Cell T31 is T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODA Y())=3),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615,MATCH( YEAR(EDATE(TODAY(),-12))-1,EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0)-SUM(T$30:T30),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615, MATCH(YEAR(EDATE(TODAY(),-12)),EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0))-SUM(T$30:T30)... where Cell T30 gives the summated value for the current Financial Year Any simpler formula for the above? Thanks San |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi,
Am Tue, 26 Aug 2014 05:03:04 -0700 (PDT) schrieb San: T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODA Y())=3),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615,MATCH( YEAR(EDATE(TODAY(),-12))-1,EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0)-SUM(T$30:T30),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615, MATCH(YEAR(EDATE(TODAY(),-12)),EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0))-SUM(T$30:T30)... where Cell T30 gives the summated value for the current Financial Year if you have to sum EMV!AA2:AA3615 try: =SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
Hi again,
Am Tue, 26 Aug 2014 14:53:05 +0200 schrieb Claus Busch: if you have to sum EMV!AA2:AA3615 try: =SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615) sorry, there is an error. Column C are numbers of month so you do not need MONTH Here is a shorter suggestion: =SUMPRODUCT((((YEAR(EMV!A2:A3615)=YEAR(TODAY())-1)*(EMV!C2:C3615=4))+((YEAR(EMV!A2:A3615)=YEAR(TO DAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
On Tuesday, August 26, 2014 7:00:58 PM UTC+5:30, Claus Busch wrote:
Hi again, Am Tue, 26 Aug 2014 14:53:05 +0200 schrieb Claus Busch: if you have to sum EMV!AA2:AA3615 try: =SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615) sorry, there is an error. Column C are numbers of month so you do not need MONTH Here is a shorter suggestion: =SUMPRODUCT((((YEAR(EMV!A2:A3615)=YEAR(TODAY())-1)*(EMV!C2:C3615=4))+((YEAR(EMV!A2:A3615)=YEAR(TO DAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Thanks Claus for this simple formula. I have only removed the "month" tag in the formula as C column is giving the serial no. of the month, which I presume is not a date field any more. After that it worked fine! San =SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())-1),--(EMV!C2:C3615=4),EMV!AA2:AA3615)+SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())),--(EMV!C2:C3615<=3),EMV!AA2:AA3615) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-Color Scale Vlookup for Current Month/Previous/Pre-Previous | Excel Discussion (Misc queries) | |||
Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
Previous Month End | Excel Discussion (Misc queries) | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
automatically update chart plotting current month and previous 6 | Charts and Charting in Excel |