Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

In row B1:CP1 are dates of the month. In B4:CP4 I have entered continous values using…

  1. User

    In row B1:CP1 are dates of the month. In B4:CP4 I have entered continous values using 'm' and 'n' to mark a specific series. The following formula accurately provides me the date of the start of the series

    =IFERROR(IF(COUNTIF(B4:CO4,"m")>0,INDEX(BB1:COCO1,MATCH("m",B4:CO4,0)),""),"")

    The formula below does not provide me an accurate date of the of the end of the series =IFERROR(IF(COUNTIF(B4:CP4, "m") + COUNTIF(B4:CP4, "n") > 0,INDEX(B1:CPB1:CP1, MATCH(TRUE, COUNTIF(B4:CP4, {"m","n"}) > 0, 0)),""),"")

  2. Assistant

    To find the date for the end of the series marked by 'm' and 'n', we can modify your existing formula to correctly identify the last occurrence of either "m" or "n" in the range, allowing us to accurately capture the date from the corresponding header row.

    Here is a revised formula that should work for you:

    =IFERROR(INDEX($B$1:$CP$1, MAX(IF((B4:CP4="m") + (B4:CP4="n"), COLUMN(B4:CP4)-COLUMN(B4)+1)), ""), "")
    

    Explanation of the Formula:

    1. IFERROR(...): This part helps to return a blank cell in case of any errors.
    2. MAX(...): This function calculates the maximum column index where either "m" or "n" appears.
    3. IF((B4:CP4="m") + (B4:CP4="n"), COLUMN(B4:CP4)-COLUMN(B4)+1): Inside the MAX function, this checks each cell in the range B4:CP4. If it contains "m" or "n", it returns the adjusted column index (1-based) relative to B4.
    4. INDEX($B$1:$CP$1, ...): This retrieves the date corresponding to the last column with "m" or "n".

    Important Note:

    • When you enter this formula, remember it should be confirmed as an array formula. In older versions of Excel, this means you would need to press Ctrl + Shift + Enter. In Excel 365 and Excel 2021, just pressing Enter will suffice because they handle array formulas natively.

    Make sure to adjust the ranges ($B$1:$CP$1 and B4:CP4) according to your actual data layout. This formula will accurately provide the date corresponding to the last occurrence of either "m" or "n" in your specified row.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.