How to use Excel to find the nth date based on the size of the list of those dates

Question

I have a table like this one with date values for planned and actual dates

Study Acronym	Milestone short name	Actuals	Planned
STUDY1	SCON
STUDY1	SCON		21/08/2023
STUDY1	SCON		31/08/2023
STUDY1	SCON		21/09/2023
STUDY1	SCON		02/10/2023
STUDY1	SCON		06/10/2023
STUDY1	SCON		20/10/2023
STUDY1	SCON		27/10/2023
STUDY1	SCON		02/11/2023
STUDY1	SCON		29/11/2023
STUDY1	SCON		01/02/2024
STUDY1	SCON		21/03/2024
STUDY1	SCON		06/12/2024
STUDY1	SCON	25/08/2023	25/08/2023
STUDY1	SCON	18/09/2023	18/09/2023
STUDY1	SCON	19/09/2023	19/09/2023
STUDY1	SCON	21/09/2023	21/09/2023
STUDY1	SCON	06/10/2023	06/10/2023
STUDY1	SCON	09/10/2023	09/10/2023
STUDY1	SCON	09/10/2023	11/10/2023
STUDY1	SCON	11/10/2023	10/10/2023
STUDY1	SCON	12/10/2023	04/10/2023
STUDY1	SCON	24/10/2023	20/10/2023
STUDY1	SCON	25/10/2023	20/10/2023
STUDY1	SCON	01/11/2023	10/11/2023
STUDY1	SCON	09/11/2023	08/11/2023
STUDY1	SCON	22/11/2023	06/10/2023
STUDY1	SCON	09/12/2023	09/12/2023
STUDY1	SCON	12/12/2023	08/12/2023
STUDY1	SCON	10/01/2024	31/01/2024
STUDY1	SCON	12/01/2024	31/01/2024
STUDY1	SCON	01/02/2024	01/02/2024
STUDY1	SCON	05/02/2024	05/02/2024
STUDY1	SCON	05/02/2024	08/02/2024
STUDY1	SCON	27/02/2024	27/02/2024
STUDY1	SCON	18/03/2024	18/03/2024
STUDY1	SCON	20/03/2024	02/02/2024
STUDY1	SCON	20/03/2024	06/03/2024
STUDY1	SCON	21/03/2024	06/02/2024
STUDY1	SCON	21/03/2024	21/03/2024
STUDY1	SCON	22/03/2024	29/03/2024
STUDY1	SCON	30/04/2024	30/04/2024
STUDY1	SCON	13/06/2024	13/06/2024
STUDY1	SCON	25/06/2024	08/12/2023

I want to automate an equation/formula/macro to calculate the date that 25% SCON occurred. I was able to do it manually with the following steps. There must be a way to remove manual steps here, as I have to repeat this calculation for over 50 tables.

The steps I took:

• 1. Count number of planned SCON dates for Study 1
• 2. Multiply this number by 0.25 to find the 25%th date value = n
• 3. Go down list of ACTUAL dates and count the nth value
• 4. Find the date associated with this value

By using the table above, this manual working returned: 44 Planned SCON dates, 25% of this is the 11th date. So the value I need is the 11th date value in the Actuals column : 25-10-23

Is there a way to make this more intuitive/simpler? Or a way to automate it so that I don't have to manually take the 4 steps above each time.

I have included an image of the table with the date highlighted as i've worked it out.

Any and all help would be massively appreciated!!!

Answer 1

Since you mentioned that you have to use this for more than 50 tables, you may want to create a named function in the workbook (you could use any of the nice solutions already provided). Here's an example using a named function.

Steps

Define a formula act_date with parameters for input ranges and criteria (r_s, r_ms, r_act, r_pl, study, ms)

1. Test this in a cell

=LAMBDA(r_s,r_ms,r_act,r_pl,study,ms, LET(
study_and_ms_matched, COUNTIFS(r_s, study, r_ms, ms),
has_value, LAMBDA(a, LEN(TRIM(a)) > 0),
_25th_percent_pl, ROUNDUP(0.25 * ROWS(FILTER(r_pl, has_value(r_pl) > 0) * study_and_ms_matched), 0),
act_date_at_25th, INDEX(FILTER(r_act, has_value(r_act) * study_and_ms_matched), _25th_percent_pl), act_date_at_25th)
)($A$2:$A$45, $B$2:$B$45, $C$2:$C$45, $D$2:$D$45, "STUDY1", "SCON")

• Store study and milestone matches to use them later; COUNTIFS returns 1 if both are matched
• Define helper function has_value to test for empty cells - optional
• _25th_percent_pl match both conditions by using has_value(r_pl) > 0) * study_and_ms_matched; please change ROUNDUP if needed
• Test by providing parameters ($A$2:$A$45, $B$2:$B$45, $C$2:$C$45, $D$2:$D$45, "STUDY1", "SCON")

2. Add the LAMBDA part (without the parameters) in Name Manager with name and description. When defined with workbook scope, the function is available in all worksheets. It will be available in other workbooks also when you copy one the worksheets to those.

3. Test it by entering in a cell:

=act_date($A$2:$A$45, $B$2:$B$45, $C$2:$C$45, $D$2:$D$45, "STUDY1", "SCON")

Optional - add conditional formatting for the Actuals ' range.

---

I use Excel Labs | Microsoft Garage for entering and testing long formulas.

=LAMBDA(r_s, r_ms, r_act, r_pl, study, ms,
    LET(
        study_and_ms_matched, COUNTIFS(r_s, study, r_ms, ms),
        has_value, LAMBDA(a, LEN(TRIM(a)) > 0),
        _25th_percent_pl, ROUNDUP(
            0.25 *
                ROWS(
                    FILTER(r_pl, has_value(r_pl) > 0) *
                        study_and_ms_matched
                ),
            0
        ),
        act_date_at_25th, INDEX(
            FILTER(r_act, has_value(r_act) * study_and_ms_matched),
            _25th_percent_pl
        ),
        act_date_at_25th
    )
)($A$2:$A$45, $B$2:$B$45, $C$2:$C$45, $D$2:$D$45, "STUDY1", "SCON")

Answer 2

Nth Value Based on List

• I used 0.5 instead of 0.25 to get multiple results for a small dataset.
• I used >0. You might consider <>"".
• I used INT. You might consider a flavor of ROUND.

=LET(s,Table2[Study Acronym],m,Table2[Milestone short name],
        a,Table2[Actuals],p,Table2[Planned],factor,0.5,
    u,s&"|"&m,
    d,DROP(REDUCE("",UNIQUE(u),LAMBDA(rr,r,LET(
        f,u=r,
        fn,INT(ROWS(FILTER(p,f*(p>0)))*factor),
        VSTACK(rr,IFERROR(CHOOSEROWS(
           FILTER(a,f*(a>0)),fn),""))))),1),
    HSTACK(UNIQUE(HSTACK(s,m)),d))

Answer 3

=LET(i,(A2:A45=A2)*(B2:B45="SCON"),INDEX(TOCOL(IFS(i,IFS(C2:C45,C2:C45)),2),ROUNDUP(ROWS(TOCOL(IFS(i,IFS(D2:D45,D2:D45)),2))/4,)))

First i is declared: an array on the rows containing "STUDY1" (equal to A2) in column A and "SCON" in column B. Next we use IFS to create an error on all 0 values in the i array and another IFS to create an error on non value cells in given range. the 2 argument in TOCOL filters out all errors and only the date values remain. so if we count the rows of the "filtered" D-column values and divide by 4 (equals *25%) and round up to integer, we can use that inside the indexed "filtered" C-column values.

Answer 4

If you have Excel 365, you can use a formula as well:

=LET(cntRows,ROWS(FILTER(Tabelle1,(Table1[Study Acronym]="STUDY1"))),
resultRow,cntRows/4,
INDEX(FILTER(Tabelle1[Actuals],Table1[Actuals]<>""),resultRow))

• It first counts the number of rows - filtered by the Study name.
• Then divides the result by 4 (= 25%)
• Then uses INDEX and FILTER to return the result.

Don't forget to format the cell as date!

Answer 5

You can automate this process with a bit of VBA in Excel. Here's how you can do that.

Open your Excel file and press ALT + F11 to open the VBA editor. Then, insert a new module by clicking Insert > Module, and paste the following code:

Sub Calculate25PercentSCON()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim plannedCount As Long
    Dim targetRow As Long
    Dim actualDates As Range
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    ' Count the number of planned SCON dates
    plannedCount = Application.WorksheetFunction.CountA(ws.Range("E2:E" & lastRow))
    
    ' Calculate the 25% index
    targetRow = Application.WorksheetFunction.RoundUp(plannedCount * 0.25, 0)
    
    ' Find the corresponding actual date
    Set actualDates = ws.Range("D2:D" & lastRow)
    
    i = 0
    For Each cell In actualDates
        If cell.Value <> "" Then
            i = i + 1
        End If
        If i = targetRow Then
            MsgBox "The 25% SCON date is: " & cell.Value
            Exit For
        End If
    Next cell
End Sub

The macro counts the planned dates, finds the 25% index, then locates and shows the corresponding date in the actuals column. You just run it and it tells you the 25% SCON date.