Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater than a month
I have these two formulas below working great. I have been asked to modify
it by doing the calculation for a period greater than one month. I currently identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to calculate if the data is between two dates. Thanks. =COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724))) =AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater than a month
=COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G 17,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$ 5724)))
What is that formula supposed to be doing? If you want an average based on a date range... A1 = lower date boundary, for example 1/1/2010 B1 = upper date boundary, for example 1/15/2010 Array entered**: =AVERAGE(IF(date_range=A1,IF(date_range<=B1,range _to_average))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "PAL" wrote in message ... I have these two formulas below working great. I have been asked to modify it by doing the calculation for a period greater than one month. I currently identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to calculate if the data is between two dates. Thanks. =COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724))) =AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater than a month
Hi,
Here's a modified AVERAGE formula that does what you want. Note we are now compoaring full dates so we can drop the TEXT bits of the formula. The date ranges are now G17 (Earlier date) and f17 (Later date) =AVERAGE(IF(Work!$N$3:$N$5724=G17,IF(Work!$N$3:$N $5724<=F17,IF(Work!$O$3:$O$5724<"",Work!$B$3:$B$5 724)))) The other formula seems to ne making hard work of doing something simple and could be modified to =SUMPRODUCT((N3:N14=G17)*(O3:O14="")) or now to your new requirement to include a start end date =SUMPRODUCT((Work!N3:N5724=G17)*(Work!N3:N5724<=F 17)*(Work!O3:O5724="")) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PAL" wrote: I have these two formulas below working great. I have been asked to modify it by doing the calculation for a period greater than one month. I currently identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to calculate if the data is between two dates. Thanks. =COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724))) =AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
Subtract a future month from the current month to get remaining m. | Excel Worksheet Functions | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |