google-sheetsgoogle-sheets-formula

Calculate difference of values in specific range of time in Excel


excel sheet with weights

In this Google Sheet, i want to calculate the difference of weight between specific time (find the value of weight between 20:55 until 21:05 time o'clock and calculate difference with the last entry of the weight)

In my case must be : 477.93 - 437.97 = 39.96

Must say that this google sheet updated every day in several times

***update! the solution is on this formula :

=map(B2:B, C2:C, lambda(time, weight, if(isbetween(time, value("20:55"), value("21:05")), weight - offset(C2,counta(C3:C99999),0,1,1), iferror(ø) ) ))

THANK YOU SO MUCH!

Solution

  • Clear column I2:I and put a formula that uses map() and offset() in cell I2, like this:

    =map(B2:B, C2:C, lambda(time, weight, 
      if(isbetween(time, value("20:55"), value("21:05")), 
        weight - offset(weight, -1, 0), 
        iferror(ø) 
      ) 
    ))
    

    See map() and offset().