I'm not sure I understand. First you said...
"The Modality(Helper) correctly counted two BL masters for the same course as two separate BL masters:"
but then you go on to say...
"the Modality(Helper) formula counts its as two, when it should be one."
Are you able to show a screenshot of that specific section of the sheet?
Sorry about that. Yes, the formula does what it is supposed to do. It counts both BL masters. The Modality(Helper) cells BL-6w/BL-10w/OL-6w. This is "correct".
However, I don't want it to count both BL. I would prefer it to count as 1 blended, 1 online. If it doesn't it says there are a total of, say, 12 BL, 12 OL, and 12 BLO/OL, but there are only 11 required courses, so the percentage with each type of master completed is 114%.
See the percentage (see red outline) is over 100% because the # of BL masters is 8 (see green outline) even though there are only 7 required courses. This happens becauses one of the required courses (see blue outline) has two OG (or BL) masters.
In the Kendall College course list sheet, see what shows in the Modality (Helper) column:
I adjusted your awesome formulas for OG (since in this college they don't have BL, blended, courses, they have fully on-ground or OG courses). For CUL 110, the same course, CUL 110, meets either for 1 day or 5 days, both on-ground.
I just want it to count 1 OG master for this course, not two, so the percentage (# of OG masters divided by number of required courses) is no more than 100%.
Is there a way around this? That is, when it counts how many required or elective courses in a program have a completed BL (or OL) master, when it looks at Modality(Helper), it only counts either modality once, rather than twice (if there happens to be more than one version of a BL or OL master)?
So, something like, if it contains "BL", add once, not for each BL that is in Modality(Helper)?
How about this...
Can you place both sheets in a folder then save a copy of the folder (this keeps links in place). Next... Remove/replace sensitive/confidential data. Then publish both sheets as "Edit by anyone" and share those links here?
This will allow me to see the data that is missing from your screenshots such as the full list in the [Required] field, all of the parsed columns, and the remaining Cull 110 rows that aren't shown in the second screenshot.
The formulas should actually NOT be counting the purple cell twice as it should only be counting cells where the [Modality Helper] contains "OG". The rows that aren't shown in your second screenshot should be blank in the [Modality Helper] column.
I can try to do the copies as you recommended.
I checked the required courses for this program. It requires the following courses:
CUL 110,KCUL 111,KCUL 115,KCUL 120,KCUL 121,KCUL 125,KCUL 127
The # of BL masters should be 5:
CUL 110 has "OG-1d/OG-5d" in Modality(Helper)
KCUL 111 has "OG-20d"
KCUL 115 has "OG-5d"
KCUL 120 has "OG-10d"
KCUL 121 has nothing (since none are completed)
KCUL 125 has "OG-5d"
KCUL 127 has nothing
So, total courses with OG masters should be 5, not 8
I removed each required course, one at a time, to see how it affected the total. When I removed KCUL 111, it reduced the total from 8 to 6. When I tested KCUL 125, it did the same thing. (If I remove CUL 110, KCUL 115, KCL 120, it reduces the total by one. Obviously when I tested removing KCUL 121 or KCUL 127, the total didn't change (they have no OG masters).
So, for some reason, KCUL 111 and KCUL 125 are counting twice.
It shouldn't be counting twice considering there is only one entry for each. That's odd. You may want to go ahead and also reach out to support. You may need a "back-end refresh" on the sheet because that seems a little "buggy". In the mean time... Try the following:
Clear cookies and cache
Log back in