1. M

    Need normal formula instead volatile

    Hello,</SPAN> At present using following volatile formula does it is possible to be converted in the normal formula</SPAN></SPAN> Book1EFG1233 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 13Sheet1Cell FormulasCellFormulaG3=MATCH(1000,INDEX(FIND(" | "&ROW(INDIRECT("1:999"))&" | "," | "&E3&" | "),0)) Thank...
  2. T

    does IFS() calculate the redundant terms in background

    to save wasted processing I sometimes use =if(A1="","", [heavy formula]) which clearly works, as the heavy formula isn't calculated when its redundant. But if i use IFS() to do the same (as per the below) =IFS(A1="", "", A1=0, "", 1=1, [heavy formula]) it should do the same... BUT when...
  3. J

    Summing values from previous rows?

    Hi, Wondering best way to do this? I read about using offset but will be volatile right Example: A10: =SUM($A$2:A9) Any easier way to do this in a table?
  4. J

    Volatile IF Statements

    =IF(AND(W6<>"",VALUE(W6)=VALUE(TODAY()+1)),"Due in 24 Hours",1) Hello, Why is the above formulas second part of the If statement volatile? This bit: VALUE(W6)=VALUE(TODAY()+1)) Many thanks.
  5. J

    Volatile Time calculations

    Hi, I'm wanting to calculate a time from when a user clocks in to the current time though unsure the best way to go about it. Currently I have a cell with now() Then subtract the clock in time from current time. Though this makes all the cells volatile right? Is there a better way to do this...
  6. P

    Please help – optimizing heavy workbook containing volatile functions

    Dear all, I have a workbook that recently became heavy and unstable. The simplest of data entries and calculations are now extremely slow and laggy. Furthermore, this is a workbook that will need to be constantly updated with more data. I’m looking for suggestions on how to optimize what I...
  7. V

    Way to clean up Dynamic Index (dirty/volatile)

    Hello all, I've got a table with date and time of sportmatches being played. In another sheet I would like to have the most 7 upcoming games. a short/simple example: <tbody> A B C D E 12 Round Date Time yearfrac key/row# 13 1 11 aug 18:00 0,005555556 1 14 12...
  8. D

    Conditional Formatting always Volatile = speed killer. Idea to optimize?

    So in trying to troubleshoot my laggy file, I got rid of all Volatile functions (which I understand to be speed killers), only to learn that the #1 culprit may in fact be the 120 conditional formatting (CF) rules I have, which my research indicates are always volatile, which I guess makes...
  9. D

    Making NOW() "less" volatile(?)

    I'm trying to rid my file of all VOLATILE functions; the only one remaining is a single NOW() function, however there are a few hundred cells who depend on that Now() value, so my understanding is that even though only on cell is technically 'volatile', I've effectively turned it into several...
  10. C

    Cell Matching Without Using Volatile Functions

    I have a very big WS that I am trying to speed up by getting rid of volatile functions like Indirect. Occasionally the spread sheet changes which makes things even more difficult because of Indirect. Column A numbers 1-24 and repeats like hours on a clock. Column C represents a 24 hour period...
  11. M

    Faster to use only one cell with a volatile function and have other cells access that cell?

    Right now I have lots of cells in my table that use the NOW function, which is volatile. Would it be faster to just have one cell outside of my table that would store the value of NOW and then have all the other cells reference that cell address in their calculations, or would that be a waste of...
  12. B

    Dynamic lookup without volatile functions

    Hi folks, I've got a (reasonably complicated) issue that I thought I had solved by learning how to use the INDIRECT function, but now I'm being told that my solution is inadequate because people don't like being asked to save changes when they quit if they know they didn't make any changes...
  13. C

    SUMIFS and non-volatile alternative to INDIRECT

    Hi everybody, I have a workbook with a data set and a relatively large report. The raw data are pulled from some ERP software and stored in a table in a worksheet. Then, I use a combination of SUMIFS and INDIRECT functions to organise the data in a report in another worksheet. Basically...
  14. J

    What if Analysis - Data Tables

    Hi - I've been given a model which makes use of What If Analysis - Data Tables. The model already has lots of volatile functions in it which I'm in the process of removing but I wondered if someone could quickly answer whether this tool is Volatile in nature? The alternative is Excel build's...
  15. T

    Save Prompt using Index-Match lookup

    I have a workbook that prompts me to save even if I immediately close it without even moving the cursor location. I have narrowed it down to the following formula (if I delete the column with this formula then the prompt stops). Note: The named ranges are dynamic and on a separate tab named...
  16. S

    Excel 2010 - Accept a volatile function

    How can I force excel 2010 to accept a volatile function. Note I don't need the date Now() to recalculate on reload every time. The cell just doesn't accept my formula as a formula though its pretty basic. All it does is compare dates in an if. so on first go I used the now function to compare...
  17. A

    Application Volatile Not Working?

    Hi everyone, So I wrote a short function to sum the values in a defined range only if the text is not black, but the formula is not automatically calculating. I share this sheet with others who won't be willing to force a calculation so it's imperative that it works with as little interference...
  18. A

    Non-volatile substitute for INDIRECT function

    Hi All, I'm currently working on a very large file and excel is consistently crashing on me. I believe this is largely due to the number of indirect functions I'm using. I have 31 sheets, named D1 - D31, for each day of the month. I then have a data sheet and 6 sheets analysing the monthly...
  19. K

    How to make my VBA function volatile?

    Hello, I have written a custom function in VBA that includes a random component, with VBAs RND function, but unfortunately it doesn't behave as a volatile function when I use it in my worksheet. For example, it doesn't automatically recalculate when I hit the delete key on a blank cell. The...
  20. H

    Make a formula more efficient and lose volatility

    Hello people, I created some time ago a spreadsheet that my team uses to keep a record of some work that we do. The basic format is that each task takes up a row of the spreadsheet, with an Id, Title etc and a cell containing any notes that people may add to the task. This notes field has text...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back