arrays - Excel: Subtract 2 values based on multiple criteria to get message response rate -

-

hi i'm trying track response rates in various message threads , having trouble writing formula. included link sample data set sudo formula below. appreciated!

data set link:

https://docs.google.com/spreadsheets/d/1tgocxxxtlntizsdqyvglespfjzdi73y61cfzcskrgvm/edit?usp=sharing

sudo formula:

if ("actionable" = "y")

then find next row has:

a) next "message #" in sequence , b) same "thread_id" , c) sent other user = "n"

then subtract "date" value of second row first row

data set:

user_id thread_id message_id date fromuser_id sent other user message # actionable responsetime

3198 5555 22115 1/22/2016 20:41:00 1109 y 1 y
3198 5555 22217 1/25/2016 4:22:00 3198 n 2 n
3198 5555 22225 1/25/2016 15:03:00 1109 y 3 y
3198 5555 22226 1/25/2016 15:04:00 1109 y 4 n
3198 5555 22228 1/25/2016 17:01:00 3198 n 5 n
3198 5555 22229 1/25/2016 17:03:00 1109 y 6 n
3198 5555 22230 1/25/2016 17:04:00 3198 n 7 n
3198 5555 22245 1/25/2016 22:27:00 3198 n 8 n
3198 5555 22246 1/25/2016 22:36:00 1109 y 9 n
3198 5555 22247 1/25/2016 22:37:00 3198 n 10 n
3198 5723 22860 2/2/2016 1:45:00 3001 y 1 y
3198 5723 22861 2/2/2016 1:47:00 3198 n 2 n
3198 5723 22863 2/2/2016 1:48:00 3001 y 3 n
3198 5723 22864 2/2/2016 1:51:00 3198 n 4 n
3198 5723 22869 2/2/2016 1:56:00 3001 y 5 n
3198 5723 22876 2/2/2016 2:01:00 3198 n 6 n
3198 5723 22879 2/2/2016 2:03:00 3001 y 7 n
3198 5723 22880 2/2/2016 2:04:00 3198 n 8 n
3198 5723 22946 2/2/2016 19:23:00 3001 y 9 n
3198 5723 22947 2/2/2016 19:26:00 3198 n 10 n
3198 5723 22948 2/2/2016 19:26:00 3001 y 11 n
3198 5723 22949 2/2/2016 19:27:00 3198 n 12 n
3198 5723 22950 2/2/2016 19:27:00 3001 y 13 n
3198 5723 22951 2/2/2016 19:28:00 3198 n 14 n
3198 5723 22975 2/2/2016 20:57:00 3198 n 15 n
3198 5723 22977 2/2/2016 20:58:00 3001 y 16 n
3198 5723 22978 2/2/2016 20:58:00 3001 y 17 n
3198 5723 22979 2/2/2016 20:58:00 3198 n 18 n
3198 5723 22980 2/2/2016 20:59:00 3001 y 19 n
3198 5723 22981 2/2/2016 21:00:00 3198 n 20 n
3198 5723 22982 2/2/2016 21:00:00 3198 n 21 n
3198 5723 22983 2/2/2016 21:00:00 3198 n 22 n
3198 5723 22984 2/2/2016 21:00:00 3001 y 23 n
3198 5764 23019 2/3/2016 3:08:00 358 y 1 y
3198 5764 23020 2/3/2016 3:09:00 3198 n 2 n
3198 5764 23022 2/3/2016 3:12:00 358 y 3 n
3198 5764 23023 2/3/2016 3:19:00 3198 n 4 n
3198 5764 23125 2/3/2016 20:20:00 3198 n 5 n
3198 5764 23126 2/3/2016 20:26:00 358 y 6 n
3198 6188 24668 2/16/2016 23:45:00 2983 y 1 y
3198 6188 24693 2/17/2016 2:30:00 3198 n 2 n
3198 6188 24695 2/17/2016 2:43:00 2983 y 3 n
3198 6188 24696 2/17/2016 2:43:00 2983 y 4 n
3198 6188 24698 2/17/2016 2:44:00 3198 n 5 n
3198 6188 24699 2/17/2016 2:44:00 3198 n 6 n
3198 6188 24700 2/17/2016 2:45:00 2983 y 7 n
3198 6188 24708 2/17/2016 3:15:00 3198 n 8 n
3198 6188 24710 2/17/2016 3:18:00 2983 y 9 n
3198 6188 24784 2/17/2016 20:33:00 3198 n 10 n
3198 7035 28025 3/21/2016 19:59:00 3579 y 1 y
3198 7035 28068 3/21/2016 22:16:00 3198 n 2 n
3198 7035 28070 3/21/2016 22:17:00 3579 y 3 n
3198 7035 28119 3/22/2016 2:17:00 3198 n 4 n
3198 7035 28120 3/22/2016 2:18:00 3198 n 5 n
3198 7035 28167 3/22/2016 15:07:00 3579 y 6 n
3198 7035 28187 3/22/2016 17:06:00 3198 n 7 n
3198 7035 28236 3/22/2016 20:42:00 3579 y 8 n
3198 7035 28254 3/22/2016 21:43:00 3198 n 9 n
3198 7035 28356 3/23/2016 13:43:00 3198 n 10 n
3198 7035 28357 3/23/2016 13:43:00 3198 n 11 n
3198 7035 28375 3/23/2016 15:36:00 3579 y 12 n
3198 7035 28380 3/23/2016 16:05:00 3198 n 13 n
3198 7035 28392 3/23/2016 18:10:00 3579 y 14 n
3198 7035 28399 3/23/2016 19:04:00 3198 n 15 n
3198 7035 28400 3/23/2016 19:05:00 3198 n 16 n
3198 7035 28406 3/23/2016 19:17:00 3579 y 17 n
3198 7035 28436 3/23/2016 20:03:00 3579 y 18 n

in i2:j2 standard formulas.

'in i2 hours integers decimals base10 decimal of hour 'with 0.00\h\r\s cell formatting =if(h2="y", (aggregate(15, 6, d3:d999/((b3:b999=b2)*(f3:f999="n")*(g3:g999>g2)), 1)-d2)*24, text(,)) 'in j2 true time 'with [hh]:mm cell formatting =if(h2="y", aggregate(15, 6, d3:d999/((b3:b999=b2)*(f3:f999="n")*(g3:g999>g2)), 1)-d2, text(,))

fill down necessary.

  response_time


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more