Forum Discussion
Multiple regions, rolling months and percentage of answers excel formula.
DLsummer Not sure where the yes and no come into play, but the part where you use the DATE function doesn't seem to make sense.
Let's say D2 contains today's date, March 30, 2023. Then, =DATE(YEAR(D2),MONTH(D2),DAY(D2-30)) will return March 28, 2023 as it evaluates to DATE(2023, 3, 28). The 28 being the day number for February 28, 2023 which is the date in D2 minus 30 days). When you want to use a date 30 days before a given date just use ">="&D2-30.
But then I wonder what you really are trying to accomplish. A given date in D2 will always be greater than or equal to that given date minus 30 days.
1. Create a new sheet and label it "Summary".
2. In the first row of the "Summary" sheet, enter the column headers as follows: Month, Regions - ECR, WCR, SCR, CIR.
3. In cell A2, enter the formula =TEXT(TODAY(),"mmm"), which will display the current month in the abbreviated format (e.g. "Jan", "Feb", etc.).
4.To calculate the total records for the current month and region WCR in cell M2, I used the following formula:
=COUNTIFS(A:A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), A:A, "<="&EOMONTH(TODAY(), 0), B:B, "WCR")
This formula uses the COUNTIFS function to count the number of records that meet the following criteria:
• The date in column A is greater than or equal to the first day of the current month (DATE(YEAR(TODAY()), MONTH(TODAY()), 1)).
• The date in column A is less than or equal to the last day of the current month (EOMONTH(TODAY(), 0) returns the last day of the current month).
• The region in column B is "WCR".
To calculate the percentage of correct "yes" answers for the first question for region WCR in cell K3, I used the following formula:
=IF(M2=0,"",COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<="&EOMONTH(TODAY(),0),B:B,"WCR",C:C,"Yes",D:D,"Yes")/M2)
This formula uses the COUNTIFS function to count the number of records that meet the following criteria:
• The date in column A is greater than or equal to the first day of the current month (DATE(YEAR(TODAY()), MONTH(TODAY()), 1)).
• The date in column A is less than or equal to the last day of the current month (EOMONTH(TODAY(), 0) returns the last day of the current month).
• The region in column B is "WCR".
• The answer to the first question in column C is "Yes".
• The answer to the second question in column D is "Yes".
This count is then divided by the total number of records for the current month and region WCR in cell M2 to calculate the percentage of correct "yes" answers. The IF statement at the beginning of the formula checks if the total number of records is zero and returns an empty string if it is to avoid dividing by zero.
DLsummer Difficult to follow what you are describing without seeing the file. But if the key is to capture #DIV0! errors, why not use the IFERROR function. It allows you to trap any error and return an empty string, a warning message or whatever is appropriate.
Such a formula could then look something like this:
=IFERROR( x / y, "")
where x / y represents the calculation and returns an empty string if y=0