Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
Can you provide an example?
Sounds like you will somehow need to use either the NOW() or TODAY() formula. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
Here is just an example:
10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
Here is more specifics:
I am working with financial data by period and the periods fall across multiple months. From the example below I will need to sum the data from another cell with the data in this row but as periods are completed I will need to remove these cells. For example my first calculation will include all 13 periods plus the one additional cell. But in 4 weeks I will no longer need A1. A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 "Gary''s Student" wrote: Here is just an example: 10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
If I understand you correctly, try this formula in O1.
=SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1) Simply delete the value in A1 if you don't need it in 4 weeks time, and the total will change to a new sum. HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Kesbutler" wrote: Here is more specifics: I am working with financial data by period and the periods fall across multiple months. From the example below I will need to sum the data from another cell with the data in this row but as periods are completed I will need to remove these cells. For example my first calculation will include all 13 periods plus the one additional cell. But in 4 weeks I will no longer need A1. A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 "Gary''s Student" wrote: Here is just an example: 10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
How can I do it without deleting the value? Is there a way to have it
automatically just drop the cells by looking at the current date and comparing it to the end date in another cell? "xlm" wrote: If I understand you correctly, try this formula in O1. =SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1) Simply delete the value in A1 if you don't need it in 4 weeks time, and the total will change to a new sum. HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Kesbutler" wrote: Here is more specifics: I am working with financial data by period and the periods fall across multiple months. From the example below I will need to sum the data from another cell with the data in this row but as periods are completed I will need to remove these cells. For example my first calculation will include all 13 periods plus the one additional cell. But in 4 weeks I will no longer need A1. A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 "Gary''s Student" wrote: Here is just an example: 10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
would you post how your table actually look like
and your expected result. You may need to elaborate more I will try to help. -- If this posting was helpful, please click on the Yes button Thank You cheers, "Kesbutler" wrote: How can I do it without deleting the value? Is there a way to have it automatically just drop the cells by looking at the current date and comparing it to the end date in another cell? "xlm" wrote: If I understand you correctly, try this formula in O1. =SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1) Simply delete the value in A1 if you don't need it in 4 weeks time, and the total will change to a new sum. HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Kesbutler" wrote: Here is more specifics: I am working with financial data by period and the periods fall across multiple months. From the example below I will need to sum the data from another cell with the data in this row but as periods are completed I will need to remove these cells. For example my first calculation will include all 13 periods plus the one additional cell. But in 4 weeks I will no longer need A1. A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 "Gary''s Student" wrote: Here is just an example: 10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create a forumla based on date
Could you please explain what the double unary minus is doing in this case?
You seem to be doing a calculation of the sum of the squares of the values. -- David Biddulph "xlm" wrote in message ... If I understand you correctly, try this formula in O1. =SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1) Simply delete the value in A1 if you don't need it in 4 weeks time, and the total will change to a new sum. HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "Kesbutler" wrote: Here is more specifics: I am working with financial data by period and the periods fall across multiple months. From the example below I will need to sum the data from another cell with the data in this row but as periods are completed I will need to remove these cells. For example my first calculation will include all 13 periods plus the one additional cell. But in 4 weeks I will no longer need A1. A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 "Gary''s Student" wrote: Here is just an example: 10/10/08 27 9/4/08 36 11/16/08 50 9/28/08 67 11/20/08 56 10/25/08 46 11/1/08 63 10/12/08 23 11/22/08 62 12/2/08 36 11/27/08 59 10/15/08 77 9/15/08 63 11/15/08 21 10/16/08 78 10/9/08 45 12/5/08 34 11/7/08 26 11/11/08 64 9/2/08 74 9/19/08 57 9/4/08 36 10/8/08 25 10/14/08 55 11/8/08 24 10/5/08 32 10/6/08 74 10/7/08 65 10/15/08 52 12/3/08 22 Notice the data is not even sorted. We want the sum of the November data only. =SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30)) To get October data use 10 in place of 11 -- Gary''s Student - gsnu200820 "Kesbutler" wrote: I need to create a formula based on date. I need to creat a calcualte to include other cells only if they meet certain date requirements. The cells will meet the requirements a certain period of time then will need to be removed from the formula. I am trying t automate the worksheet so I don't have to update it once a month to exclude the previous 4 weeks. How can I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I do this in Excel...create dates for tasks based on a date (long) | Excel Discussion (Misc queries) | |||
Create a formula based on date | Excel Worksheet Functions | |||
Need to create formula based on date | New Users to Excel | |||
Need to create formula based on date | New Users to Excel | |||
If/Then Forumla to create blank cell??? | Excel Worksheet Functions |