Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
3-Color Scale Vlookup for Current Month/Previous/Pre-Previous NeoFax Excel Discussion (Misc queries) 2 January 8th 10 08:04 PM
Retrieve data for previous 3, 6, 12 month given current month GB Excel Worksheet Functions 4 July 19th 07 11:58 PM
Previous Month End Tristan Excel Discussion (Misc queries) 7 September 9th 06 12:25 PM
copy worksheet from previous month and rename to current month Dan E. Excel Programming 4 December 8th 05 10:40 PM
automatically update chart plotting current month and previous 6 Jane Charts and Charting in Excel 1 September 1st 05 10:19 PM


All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"