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