AGGREGATE function not working when reaches maximum range value

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
793
Office Version
  1. 365
Platform
  1. MacOS
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.

Screenshot 2023-03-15 at 15.18.51.png


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:

Screenshot 2023-03-15 at 15.29.19.jpg


Any help much appreciated!

Thanks ion advance,

Olly.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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!
 
Upvote 0
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!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top