## Google Sheets Functions – OFFSET

Sometimes we spend time setting up beautiful spreadsheets only for us to have to add rows or columns afterwards, which then messes up our formulas and we have to change them. In this post, we’re going to look at a couple of examples of the OFFSET function, which will help us create more dynamic formulas. What we mean by this is that the formula will adapt to changes made to the spreadsheets, quite often where rows and columns have been added. Example 1 – Creating dynamic ranges to maintain an average formula Here we have some marks for some students. (To those who have been following my posts, honestly, I’m not obsessed with exam marks, they just make good examples!) In cell B6 I’ve added an AVERAGE function to work out the average of the marks. But I now have another student to add who’s done the test. I add a row and insert the student’s details, but as you can see this hasn’t changed the average figure. If we look at the formula, it hasn’t changed despite there being an extra row. We can solve this by using the OFFSET function in the AVERAGE one. In cell E6, I’ve added the following formula: OK, so what’s happening? Well let’s look at the syntax of the function to understand it better. The OFFSET function has 3 main parts: cell reference:...

Read More
## Recent Comments