Mar 29 2023 10:07 AM - edited Mar 29 2023 10:18 AM
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 together a calculation that will pull the information automatically without having to recalculate each month.
I started the rolling date for the month with the calculation =COUNTIFS(E2:E200,”CR”,D2:D200,”>=“&DATE(YEAR(D2),MONTH(D2),DAY(D2-30))) where E2:E200 is the region; D2:D200 is the dates. But I have a feeling this is not right. Advice?
Mar 29 2023 09:24 PM
@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.
Apr 05 2023 11:50 AM
Apr 05 2023 09:55 PM
@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