Need Help finding Error in Formula

[JasonMcQueen]

Original message:

Hi,

could someone please take a look at the formulas in columns X and Y? I've been staring at it for an hour and cant see why its not working correctly and need a (better) different pair of eyes.

I suspect its something to do with the date because if I roll A3 back to December 2020, it works. But when I move it forward to January 2021, it breaks with a DIV0 error, even though there is data that should be inrange, essentially looking back on the last 12 month range from the date.


**Updated**

What I'm trying to get the formula to do is return a percentage over the previous 12 month date range from the date in A3 to give a 12 month rolling percentage for "Yes" values.

It looks like its returning a DIV/0 error because there's no data yet for 2021, but I'd like it to still count the valid date for the previous months. Looking at the data on the accident log for 'fabrics' as an example, there are 6/7 'YES' in W and 1/7 in AA so percentages should be 86% and 14% respectively. The formula seems to be ignoring these at the moment due to the date range.


-------------------------------------------------------------------------------
Hi,

could someone please take a look at the formulas in columns X and Y? I've been staring at it for an hour and cant see why its not working correctly and need a (better) different pair of eyes.

I suspect its something to do with the date because if I roll A3 back to December 2020, it works. But when I move it forward to January 2021, it breaks with a DIV0 error, even though there is data that should be inrange, essentially looking back on the last 12 month range from the date.

[AliGW]

There are no 2021 dates in column F of the accident log. Try adding one and see what happens!

[JasonMcQueen]

Ah, I thought that could be it but I wanted it to still return the value from the previous 12 month date range and ignore that there arent any entries yet for 2021.

[AliGW]

OK - can't help at the mo as I am about to go into a meeting, but I am sure someone else will be able to. Perhaps if you just clarify exactly what results you expect to see? A non-working formula is hard to troubleshoot without knowing the end goal. Thanks!

[JasonMcQueen]

Updated the requirements of the formula in first post.

[hrlngrv]

Replace the 'ACCIDENT LOG'!$F:$F,">="&DATE(YEAR($A$3),1,1) criterion with 'ACCIDENT LOG'!$F:$F,">="&EOMONTH($A$3,-12-(DAY($A$3)<16))+1 to get rolling 12ish month averages.

[JasonMcQueen]

My mistake. I only changed the first part of the formula

Thanks.

[AliGW]

Updated the requirements of the formula in first post.

Jason - don't do that, please - add new or extra information chronologically in the thread, otherwise it becomes very difficult to follow!

I will restore your original post whilst keeping the new detail.

[JasonMcQueen]

No problem. I was just trying to keep it concise.