Results 1 to 4 of 4

Thread: Find 1st and last specific text between 2 dates and subtract thie adjacent value

  1. #1
    Neophyte Sam67's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    Excel 2010

    Find 1st and last specific text between 2 dates and subtract thie adjacent value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I'm trying to find a formula that find the first instance in January when WL01(column B)occur and the last time WL01 occur (column B) and then subtract the last instance (column C) value form the first instance value(column C) from each other, 125-50=75

    A B C
    2019/01/02 WL01 50
    2019/01/12 WL02 120
    2019/01/06 WL06 30
    2019/01/14 WL01 125
    2019/01/23 WL02 135
    2019/01/31 WL06 65

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,649
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    try this =INDEX(C1:C6,AGGREGATE(14,6,(1/((B1:B6=B1)))*ROW(B1:B6),1))-INDEX(C1:C6,AGGREGATE(15,6,(1/((B1:B6=B1)))*ROW(B1:B6),1))

    ( should be adapted if you do not start with row 1)
    Thank you Ken for this secure forum.

  3. #3
    Neophyte Sam67's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    Excel 2010
    Hi, thanks it works!!


    Is there a way I can tweak that formula to let it look between 2019/01/01 and 2019/01/31 and between 2019/02/01 and 2019/02/28 using dates ..(and other months) if I put the start and end dates of all month in a column?

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    882
    Articles
    0
    Excel Version
    Excel 2013
    Try formula
    Code:
    =INDEX(C1:C6;AGGREGATE(14;6;(1/((A1:A6=$A$4)))*ROW(A1:A6);1))-INDEX(C1:C6;AGGREGATE(15;6;(1/((A1:A6=$A$1)))*ROW(A1:A6);1))
    $A$4 is last Date
    $A$1 is first Date
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •