Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO I SET UP AN IF STATEMENT THAT WILL UPDATE MONTHLY
I AM WORKING ON A SPREADSHEET TO TRACK MONTHLY SALES. I WANT TO DO AN IF
STATEMENT THAT WILL TELL ME IF SALES ARE UP OR DOWN COMAPAIRING THE LAST MONTH TO THE PRIOR MONTH. I KNOW HOW TO DO THE IF STATEMENT. BUT WHAT I DONT KNOW HOW TO DO IS SET IT UP TO UPDATE EACH TIME A NEW MONTH ENDS. FOR EXAMPLE I WANT TO COMPARE SALES FOR DEC WITH NOV WERE THEY UP OR DOWN. NOW WHEN JANUARY ENDS I WANT TO COMPARE JANUARY WITH DECEMBER. I WOULD LIKE THE IF STATEMENT TO UPDATE TO TAKE THE NEW INFORMATION AND COMPARE IT WITH THE PREVIOUS MONTH WITH OUT HAVING TO GO IN AND ADJUST THE IF STATEMENT EACH TIME. CAN THIS BE DONE AND IF SO HOW? I AM USING EXCEL 2003 AT THIS POINT AND TIME. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO I SET UP AN IF STATEMENT THAT WILL UPDATE MONTHLY
Please don't post in all upper case letters, it makes you post harder to
read and is considered shouting in the Newsgroups. I would put the month numbers of the the last month and the month before that in two cells, say E2 & F2: Last Month in E2: =MONTH(TODAY()-DAY(TODAY())) Month before that in F2: =IF(E2-1=0,12,E2-1) With the dates in Column G and the amounts in Column H, to calculate the the difference between Last Month and the month before that use: =SUMPRODUCT((MONTH(G1:G730)<"")*(MONTH(G1:G730)=E 2)*(H1:H730)-(MONTH(G1:G730)<"")*(MONTH(G1:G730)=F2)*(H1:H730) ) You say you know how to do the IF() statement so I am sure that you can incorporate it into your IF() statement as you require. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "DEVONA" wrote in message ... I AM WORKING ON A SPREADSHEET TO TRACK MONTHLY SALES. I WANT TO DO AN IF STATEMENT THAT WILL TELL ME IF SALES ARE UP OR DOWN COMAPAIRING THE LAST MONTH TO THE PRIOR MONTH. I KNOW HOW TO DO THE IF STATEMENT. BUT WHAT I DONT KNOW HOW TO DO IS SET IT UP TO UPDATE EACH TIME A NEW MONTH ENDS. FOR EXAMPLE I WANT TO COMPARE SALES FOR DEC WITH NOV WERE THEY UP OR DOWN. NOW WHEN JANUARY ENDS I WANT TO COMPARE JANUARY WITH DECEMBER. I WOULD LIKE THE IF STATEMENT TO UPDATE TO TAKE THE NEW INFORMATION AND COMPARE IT WITH THE PREVIOUS MONTH WITH OUT HAVING TO GO IN AND ADJUST THE IF STATEMENT EACH TIME. CAN THIS BE DONE AND IF SO HOW? I AM USING EXCEL 2003 AT THIS POINT AND TIME. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO I SET UP AN IF STATEMENT THAT WILL UPDATE MONTHLY
Hi
In addition to Sandy's suggestion, you could enter in E2 01/12/2007 and in F2 01/01/2008 then use =SUMPRODUCT((TEXT(G1:G370,"yymm")=TEXT(F2,"yymm")) *H1:H370)- SUMPRODUCT((TEXT(G1:G370,"yymm")=TEXT(E2,"yymm"))* H1:H370) -- Regards Roger Govier "DEVONA" wrote in message ... I AM WORKING ON A SPREADSHEET TO TRACK MONTHLY SALES. I WANT TO DO AN IF STATEMENT THAT WILL TELL ME IF SALES ARE UP OR DOWN COMAPAIRING THE LAST MONTH TO THE PRIOR MONTH. I KNOW HOW TO DO THE IF STATEMENT. BUT WHAT I DONT KNOW HOW TO DO IS SET IT UP TO UPDATE EACH TIME A NEW MONTH ENDS. FOR EXAMPLE I WANT TO COMPARE SALES FOR DEC WITH NOV WERE THEY UP OR DOWN. NOW WHEN JANUARY ENDS I WANT TO COMPARE JANUARY WITH DECEMBER. I WOULD LIKE THE IF STATEMENT TO UPDATE TO TAKE THE NEW INFORMATION AND COMPARE IT WITH THE PREVIOUS MONTH WITH OUT HAVING TO GO IN AND ADJUST THE IF STATEMENT EACH TIME. CAN THIS BE DONE AND IF SO HOW? I AM USING EXCEL 2003 AT THIS POINT AND TIME. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel: monthly download of 1000 members, need to update quickly | Excel Worksheet Functions | |||
How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
Excel Spreadsheet to Create/Update Monthly Calendar | Excel Discussion (Misc queries) | |||
Excel Spreadsheet to Create/Update Monthly Calendar | Excel Worksheet Functions | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) |