Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
Hi -
Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
Your requirement isn't clear to me, but hopefully the following hints will
help you find your answer. The current month is =month(today()) The previous month is =month(today())-1 Your formula has several problems with it. First, your If statement doesn't say what to do when the condition is false. Second, you never have to use And with True. True will always be true. So you can simplify your statement to: =IF(MONTH(TODAY()),SOURCE_2008!E6-SOURCE_2007!E6) Third, you're not testing the month for anything. What should the month equal? Regards, Fred. "Priss" wrote in message ... Hi - Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
Thanks for quick reply and my apologies for not being clear. Just driving me
nuts... The months are always going to be current month and a month behind due to data not being available. Not sure if that explains my needs. But I also would not reference a certain row because the spreadsheet months are entered column beside column. Other than having to go in and change the formula to reference current month. Was hoping there was a way to skip that. "Fred Smith" wrote: Your requirement isn't clear to me, but hopefully the following hints will help you find your answer. The current month is =month(today()) The previous month is =month(today())-1 Your formula has several problems with it. First, your If statement doesn't say what to do when the condition is false. Second, you never have to use And with True. True will always be true. So you can simplify your statement to: =IF(MONTH(TODAY()),SOURCE_2008!E6-SOURCE_2007!E6) Third, you're not testing the month for anything. What should the month equal? Regards, Fred. "Priss" wrote in message ... Hi - Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
Well, at least we are getting somewhere. But we need more information.
How do you know when to use the current month, and when to use the previous month? If it's the current month, what data do you want to pick up? Is it always E6? Or is the column dependent on the month? What calculation do you want to perform. Is it always the current year (2008) less the previous year? What happens when 2009 comes along? Regards, Fred. "Priss" wrote in message ... Thanks for quick reply and my apologies for not being clear. Just driving me nuts... The months are always going to be current month and a month behind due to data not being available. Not sure if that explains my needs. But I also would not reference a certain row because the spreadsheet months are entered column beside column. Other than having to go in and change the formula to reference current month. Was hoping there was a way to skip that. "Fred Smith" wrote: Your requirement isn't clear to me, but hopefully the following hints will help you find your answer. The current month is =month(today()) The previous month is =month(today())-1 Your formula has several problems with it. First, your If statement doesn't say what to do when the condition is false. Second, you never have to use And with True. True will always be true. So you can simplify your statement to: =IF(MONTH(TODAY()),SOURCE_2008!E6-SOURCE_2007!E6) Third, you're not testing the month for anything. What should the month equal? Regards, Fred. "Priss" wrote in message ... Hi - Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
The data is not always going to be in E6, depends on the month working left
to right which means F6 is April, G6 is May and so on. The calculation will always be current - previous year. And when 2009 comes into play, the plan is to duplicate calculating 2009-2008 for current and previous month data. "Fred Smith" wrote: Well, at least we are getting somewhere. But we need more information. How do you know when to use the current month, and when to use the previous month? If it's the current month, what data do you want to pick up? Is it always E6? Or is the column dependent on the month? What calculation do you want to perform. Is it always the current year (2008) less the previous year? What happens when 2009 comes along? Regards, Fred. "Priss" wrote in message ... Thanks for quick reply and my apologies for not being clear. Just driving me nuts... The months are always going to be current month and a month behind due to data not being available. Not sure if that explains my needs. But I also would not reference a certain row because the spreadsheet months are entered column beside column. Other than having to go in and change the formula to reference current month. Was hoping there was a way to skip that. "Fred Smith" wrote: Your requirement isn't clear to me, but hopefully the following hints will help you find your answer. The current month is =month(today()) The previous month is =month(today())-1 Your formula has several problems with it. First, your If statement doesn't say what to do when the condition is false. Second, you never have to use And with True. True will always be true. So you can simplify your statement to: =IF(MONTH(TODAY()),SOURCE_2008!E6-SOURCE_2007!E6) Third, you're not testing the month for anything. What should the month equal? Regards, Fred. "Priss" wrote in message ... Hi - Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions for "current" & "previous" month to calculate data
What you want is the Index function. It will take a range, and you can
specify the row and column within the range. Suppose your data is in the Range Source_2008!A1:K20 =Index(Source_2008!a1:k20,6,month(today())) will pick up cell D6 from that range, as the current month is 4 (April) If you want to copy this formula to other rows, use: =Index(Source_2008!a1:k20,row(a6),month(today())) so that the formula will adjust for each row. Within the Index function, you can use an If statement to decide whether you want the current month or the previous month. If you need help with that, let me know how you know whether you want the current or the previous month. Hope this helps, Fred "Priss" wrote in message ... The data is not always going to be in E6, depends on the month working left to right which means F6 is April, G6 is May and so on. The calculation will always be current - previous year. And when 2009 comes into play, the plan is to duplicate calculating 2009-2008 for current and previous month data. "Fred Smith" wrote: Well, at least we are getting somewhere. But we need more information. How do you know when to use the current month, and when to use the previous month? If it's the current month, what data do you want to pick up? Is it always E6? Or is the column dependent on the month? What calculation do you want to perform. Is it always the current year (2008) less the previous year? What happens when 2009 comes along? Regards, Fred. "Priss" wrote in message ... Thanks for quick reply and my apologies for not being clear. Just driving me nuts... The months are always going to be current month and a month behind due to data not being available. Not sure if that explains my needs. But I also would not reference a certain row because the spreadsheet months are entered column beside column. Other than having to go in and change the formula to reference current month. Was hoping there was a way to skip that. "Fred Smith" wrote: Your requirement isn't clear to me, but hopefully the following hints will help you find your answer. The current month is =month(today()) The previous month is =month(today())-1 Your formula has several problems with it. First, your If statement doesn't say what to do when the condition is false. Second, you never have to use And with True. True will always be true. So you can simplify your statement to: =IF(MONTH(TODAY()),SOURCE_2008!E6-SOURCE_2007!E6) Third, you're not testing the month for anything. What should the month equal? Regards, Fred. "Priss" wrote in message ... Hi - Working with (2)spreadsheets: 2008 and 2007 for comparison. Need assistance with writing formula to recognize on 2008 spreadsheet to calculate "previous" month, current if there is no data for "current" month. Right now I have: =IF(AND(MONTH(TODAY()),TRUE),SOURCE_2008!E6-SOURCE_2007!E6) whereas it's looking in a specific cell, which I rather not. Is this possible? Some columns report "current" data and others "previous". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Working out age from "Day" "Month" "Year" | Excel Worksheet Functions |