From the course: Excel: Advanced Formulas and Functions

Volatile functions - Microsoft Excel Tutorial

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial Buy for my team

Volatile functions

“

- [Instructor] Before we get started, I'm going to do this, equals, now, and then open parenthesis, close parenthesis. That's telling you that right now, it is the 7th of January, 2023, and it is now 6:23 PM. Okay, let's set that aside. This video is about volatile functions, you have to be aware of them. A function is volatile when it recalculates with every change in a workbook. And here's the list of volatile functions, INDIRECT, OFFSET, RAND, RANDARRAY, RANDBETWEEN, NOW, and TODAY. Now, over in columns I through M, I have student count, need to fill in the data, and maybe I need to make some fake data for a tutorial. All right, so I'm going to use equals, randarray. (mouse clicks) Okay, I want seven rows of data, comma. I want four columns representing the quarters, comma. What's the minimum? Let's say I want 35 to be the minimum, comma, the max 205, comma, and yes, I want it to be an integer, so I'm going to put a 1 at the end, close parenthesis, and Enter. Now, do you see that I have the blue line around the data and that shows that it's a dynamic array. One formula made all of that data. But did you notice over in D2 where I put now, it is saying that it is now 18:25. It recalculated when I changed the data in this workbook. But now, I've got my fake data and I want to show a colleague how to write a sum. So I'm going to go here and, sum, and go over here. Oh, everything's updated again. Now, I'm going to hit the key F9. See, everything updates. F9 again, everything updates. So if I want to show my colleague about how to write sums and I need fake data, I would highlight this and wait for that four-way arrow and then right click, slide, slide, let go of the right mouse button, and then copy here as values only. Now, D2 updated. Now, if I hit F9, the cell in D2 updates, but the data that I made no longer updates. You need to know about this for a couple of reasons. One, because these constantly update. If you have a lot of them, they can slow your workbook down, they can slow down the recalculation rate. And what is a lot? That's hard to pin down, but one time I did have a workbook that had about 40,000 volatile functions in it, and that sometimes caused the workbook to crash. Many of us will never work with that amount of data. So if you want to use an INDIRECT or an OFFSET and you've got 20 of them, maybe 50 of them, it's probably not going to hurt you. You just need to be aware. You also need to be aware with RANDARRAY, RAND, RANDTBETWEEN, you've got to get rid of the underlying formulas or everything is going to keep changing. And now, with the NOW function and the TODAY function, what you have to watch out for there is if you've made a calculation based on today, say how many days did it take a group of tickets to get rectified? And you base it on today, and you wind up with say, 17, 19, 50 days, and you save the workbook and you come back, all of those are going to recalculate to the new day, and then your data is going to be wrong. But then there are situations where you do want your TODAY function to update. Say if you're looking at deadlines that are out into the future and you want to look at how many days are left between today and those future dates, you might want to leave that TODAY function as a live function. So those are some of the things that you have to think about and be aware of when you deal with these volatile functions.

Contents