 I was going to do this as an in-person workshop but in our computer rooms we don't have Office 365 installed yet so I will do a different version in person and put a 365 version up here because it's really interesting we can do something really unique with it now with some newer features and what we're going to do is we're going to process this kinetics data by sampling it, linearizing it and finding a rate constant from the gradient of that linear graph so first-year chemistry students should be aware of how to do that you can probably do that on this entire data set there's like a thousand points to it Excel will handle that more software that you want to do data processing with will handle that no problem but maybe if this was 10,000 lines 20,000 lines even longer you might want to truncate that data down a little bit for either display purposes or something else so this is really more of an exercise in how to use some tools to sample this data and show off a couple of the newer features that can make that a little bit easier and more dynamic so how we'd normally do this is say we want 100 milliseconds 200 milliseconds drag down so it's a thousand and then we will go v lookup the lookup value the table array would need to be a b let's do that and then the column index number two range lookup false double click that and then insert our graph and what we've got is a sampled version of the one there we're not going to do that we're going to do something a little bit different here we're going to start with equals seq or sequence hit tab to complete it and what I want is 10 rows I know there's 10 rows one column starting at 100 milliseconds and then jumping up 100 milliseconds each time and by press return there it shoots down the entire list automatically no control shift return needed that's officially depreciated now these spill formulas are how it works going forward and what I can now do I could use v lookup but I'm actually going to use x lookup which is the newer one and my lookup value is going to be 100 my lookup array where I'm going to be looking is column a with all those times in so I'm just going to select column a I don't need to drag down any further XL is usually clever enough to not realize it needs to look up down a whole million columns it will stop where there isn't any data my return array well I want to return the absorbance in column B and that's it there are a couple of other options we could add but we don't need to use them for now and there we go 0.8208 for one of our scroll down to where it says 100 0.8208 for it's worked it's pulled that number back and we could drag that down but do we need to drag that down no because I'm going to go to k4 here and do some real clever put a hash at the end of it and what you can see is that has now highlighted that entire column where that sequence formula has generated the numbers it's picked all of them up I'm going to return that and there we are now looked up all of those numbers okay whatever I want to change something whatever I only wanted five data points I'm going to delete five rows return that crunch hits to five rows and look it has now changed this as well this has spilled down to match the exact same size of that as before so that's incredibly useful I'm going to just drag this to the right now and the same thing I'm going to divide this by a thousand but instead of dragging down I'm going to do k4 hash and there we go there is our seconds so I'm going to do this properly you do time slash milliseconds time in seconds a then log of a because log a is for the new version again ln and I'd normally type in this drag it down I'm said I'm going to put in hash and that will pick up that whole dynamic array that we generated I'm going to return that and look we've got all these numbers now have I changed this to steps of 200 milliseconds well it's shot over the maximum amount and hasn't returned anything but other than that it has picked up the numbers and all the numbers will update automatically I'm just going to give myself a little bit of space at the top for a moment now what if I wanted to change this without going into the formula I'm going to stick 100 up here as milliseconds I'm just going to format this as an input to know that I'm not going to change and I want to figure out how many steps do I need if it's taken a hundred milliseconds well the longest time I could scroll down to find it or I can just type in a max AA it's a thousand milliseconds so if I divide that by this input here I know I need 10 steps okay now where I've got rows I'm going to replace that with 10 steps and this step I'm going to do 100 milliseconds and I'm going to start there as well so now we're back to where we were except if I change this it automatically calculates that we need 20 steps to fill up the whole time and it adds them and it expands down so that's pretty good isn't it so now what I'm going to do is hire all of this and stick in a scatter graph we can top this up later but you can see that it is changing the number of data points so I changed that from 50 100 isn't it of course to make the linear data I need to drag to there and what I can do is I can add in my trend line I'm not going to add the equation of the line on there but maybe I'm going to change that back to 100 it's it's worked hasn't it now what we would normally then do so I'm just going to come over to here for a second we would want to do the line stats on this to figure out gradients intercepts and their errors for instance so I'm going to type in line st now the known y values are my log values that's n6 I'm going to again type hash and it's automatically going to pick those up the known x values are the time again I put a hash there and true true and no more control shift return just going to press enter and what line stats will now do is I'll not just get the intercept and slope and their errors but I'll get the other information to do with the line statistics there and that's all covered in the documentation I can never remember which order they all come in we're just usually are interested in those parameters and their their errors for now so I know that that is too in a bit was roughly my rate constant that I added when I created this data this is not actually real data it is it was an exponential decay with a rate constant about 2. something with some noise added to it so 2.1 plus minus 0.2 is about what we'd expect and again if I maybe reduce that step a little bit to milliseconds you can see the error goes down from 0.2 to 0.1 20 milliseconds it goes out to 0.08 so we can start sampling a larger amount of the data if I do one millisecond it's going to make a thousand steps that's accepted the whole data and you can see it's it's quite noisy now on the graph this is only actually sampling the first few so if we wanted to just subset make a subset we can do it that way so that's the fancy interactive way that dynamic ranges work now and this would be quite exciting to try and apply this to some new things later on