Forum Discussion
Multiple regions, rolling months and percentage of answers excel formula.
I have 4 regions with data for each rolling through the months and data gives yes or no. I want to capture the monthly percentage of yes for each region at the end of the month. How can I put toget...
So what worked and what I did was:
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.
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