Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarter vs Month
This formula may look familiar as I have come here for help on it before, but
my question is a tweak for it: {=COUNT(1/FREQUENCY(IF((Log!$A$6:$A$1000=A42)*(MONTH(Log!$G$ 6:$G$1000)=$E$37),MATCH(Log!$B$6:$B$1000&"",Log!$B $6:$B$1000&"",0)),ROW(1:499)))} I am focusing on the section (MONTH(Log!$G$6:$G$1000)=$E$37). The E37 is a cell in a spinner link. So as I increment the spinner the data is selected for that month, 1-12. Here is my problem. The monthly report is now a QUARTERLY report (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec). So my spinner range is now 1-4 instead of 1-12. With the above formula if the month matched the spinner cell there was Joy in Mudville. Now I need to get Jan-Mar when the spinner cell is 1, etc. Any suggestions? Am I making this harder than it really is? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarter vs Month
from (MONTH(Log!$G$6:$G$1000)=$E$37) to (Int((MONTH(Log!$G$6:$G$1000)-1)/3)+1,=$E$37) "Bigfoot17" wrote: This formula may look familiar as I have come here for help on it before, but my question is a tweak for it: {=COUNT(1/FREQUENCY(IF((Log!$A$6:$A$1000=A42)*(MONTH(Log!$G$ 6:$G$1000)=$E$37),MATCH(Log!$B$6:$B$1000&"",Log!$B $6:$B$1000&"",0)),ROW(1:499)))} I am focusing on the section (MONTH(Log!$G$6:$G$1000)=$E$37). The E37 is a cell in a spinner link. So as I increment the spinner the data is selected for that month, 1-12. Here is my problem. The monthly report is now a QUARTERLY report (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec). So my spinner range is now 1-4 instead of 1-12. With the above formula if the month matched the spinner cell there was Joy in Mudville. Now I need to get Jan-Mar when the spinner cell is 1, etc. Any suggestions? Am I making this harder than it really is? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarter vs Month
Fantastic! This was a great help.
I had to change the ",=$E$37)" + "=$E$37)" but it got me where I needed to be. Thanks again. "Joel" wrote: from (MONTH(Log!$G$6:$G$1000)=$E$37) to (Int((MONTH(Log!$G$6:$G$1000)-1)/3)+1,=$E$37) "Bigfoot17" wrote: This formula may look familiar as I have come here for help on it before, but my question is a tweak for it: {=COUNT(1/FREQUENCY(IF((Log!$A$6:$A$1000=A42)*(MONTH(Log!$G$ 6:$G$1000)=$E$37),MATCH(Log!$B$6:$B$1000&"",Log!$B $6:$B$1000&"",0)),ROW(1:499)))} I am focusing on the section (MONTH(Log!$G$6:$G$1000)=$E$37). The E37 is a cell in a spinner link. So as I increment the spinner the data is selected for that month, 1-12. Here is my problem. The monthly report is now a QUARTERLY report (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec). So my spinner range is now 1-4 instead of 1-12. With the above formula if the month matched the spinner cell there was Joy in Mudville. Now I need to get Jan-Mar when the spinner cell is 1, etc. Any suggestions? Am I making this harder than it really is? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
sumif to aggregate month to quarter | Excel Worksheet Functions | |||
Populate year, month and quarter from entered date | Excel Discussion (Misc queries) | |||
How can I set month/quarter/annual date intervals | Charts and Charting in Excel | |||
Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) |