AGGREGATE function not working when reaches maximum range value

[ollyhughes1982]

Hi,

Re the attached image, I wonder if anyone could help me. I have the formula shown and thought it was working, but recently as the last weekday has passed 6, instead of the result in D3 moving up by 1 (from 6 to the maximum possible 7), it reverts to 1. When all days have reached 7 the result should be 7, as per the logic, below:

# of Unique Weekdays completed x times. e.g. Completing 5 Unique Weekdays 5 times, gives a result of 5. i.e. 7 runs have been completed at 7 unique weekdays.

Formula in D3: =IF('All Completed nR'!B4="","",IFERROR(AGGREGATE(14,6,'All Completed nR - WD-I.'!B4#/(COUNTIF('All Completed nR - WD-I.'!B4#,">="&'All Completed nR - WD-I.'!B4#)>='All Completed nR - WD-I.'!B4#),1),1))

The formulas in A4 & B4 are as follows:
A4: =IFERROR(UNIQUE(FILTER('All Completed nR'!K4:K2003,'All Completed nR'!B4:B2003<>"")),"")
B4: =IF(A4="","",COUNTIF('All Completed nR'!K:K,A4#))

The formula seems to work until all 7 days have 7 or more each, at which points it goes back to 1. it seems like the IFERROR ‘1’ value is being triggered when all 7 have fulfilled the criteria

A clip of the source data is shown below:

Any help much appreciated!

Thanks ion advance,

Olly.

 

[awoohaw]

Hi can you post the data using the xl2bb add in, please? This is a lot of data that forum users would need to enter manually to create your scenario. If you can't do that, then please paste the data as a table. The xl2bb add in link is below. Thanks in advance!

 

[ollyhughes1982]

Hi,

Thanks, but i have now solved this with some more tinkering. I am using absolute values in the formula, i.e. using B4:B10, rather than B4#, and this seems to correct the issue. Not sure why it makes any difference, but seems to work!