VBA: Formula to only run once?

Boy, I've seen many similar questions, but I just couldn't apply other answers to this:
Using a simple formula in J88, =SUM(N88)-P1 where P1, { =INDEX(N:N,LARGE(IF(N:N<>"",ROW(N:N)),2)) } changes when data is entered into new/next row, I would like to copy this formula into multiple cells down column J, but to only have the formula calculate once. So when new a value is entered in i.e, N90, ONLY Cell J90 would calculate =SUM(N90)-P1 while the formulae in previous would cease to function - or maintain the original value.

There are blanks in column N (hence the formula in P!). Thank you!

You cannot do what you want. Either Excel evaluates all formulas whenever it feels the need to, or we can set it up so that it evaluates no formulas (in all worksheets or in specific worksheets) unless and until we tell it to, at which point Excel will evaluate all formulas as needed.

The only way to avoid the all-or-none evaluation is to copy-and-paste-value after evaluation, which replaces the formula with the value. But after that, we cannot "recover" (re-enter) the formula easily.

I suspect you are concerned about the effect that evaluating these formulas has on editing, saving and opening the workbook, etc.

A major design flaw is the use of N:N in your formulas. INDEX(N:N,...) is okay. But IF(N:N...) causes Excel to create a temporary array of 1+ million rows (in Excel 2007 and later); and in this formula, it causes Excel to compare the value of 1+ million rows.

Assuming that you have (much!) less than 1+ million rows of data in column N, it would be prudent to use a reasonable limited range -- as small as possible to improve performance, but as large as necessary to accommodate rows of data as they are added. (And sometimes, we can design the range so that it expands automagically after adding rows.) For example: IF($N$2:$N$10000<>"",ROW($N$2:$N$10000)) .

That might be sufficient to fix the problem that you wanted to fix by evaluating formulas only one time.

Aside.... Why are you writing SUM(N88) instead of just N88? If you want a function to return zero when N88 is text, consider using N(N88) instead. Technically, that would be more efficient; but admittedly, it is unlikely that we would notice the infinitesimal improvement. I bristle when I see the unnecessary use of SUM; like SUM(A1-A2), in which case SUM is completely useless. I hope you are not in the habit of doing that.

I am with you about SUM, I just used that for he example - I try to never use anything unnecessary.
Anyhow, I have zero lag in my sheet as is - using the P1 formula calculating the entire column. Isn't there any way to have a formula simply return the result as text and kill the formula? Like an auto F2,F9?

Yes, sure! Whatever this solution requires, I am game. Thank you!

I will work on a graphic to upload, thank you. How do I find the new thread?