How can this happen,,,,and.....

Before I begin let me apologise in advance if you find I am wordy in what follows.



Each weekend I run a spread sheet (actually a series of related spread sheets).

During the week I download and update data sets, and at the end of the week I run an analysis of the downloaded data. On occasion some daily data points are missing/late so I will run a simple formula to calculate the missing points at weeks end before running the in depth analysis of the data (in effect, ---- I download Tuesdays data but the data for line 45 is late and so is showing as the previous days amount, when this occurs I leave this point blank and at the end of the week I use the weeks total for line 45 - provided by the data supplier - to calculate the missing data point).

The formula I use to do this is stored at line A1 of the spreadsheet. The formula is as follows (as plugged in to this weeks series)

(CQ4-(CL4+CM4+CN4+CO4+CP4))

to save you having to figure out whats happening its simply as follows

CQ4 is the weeks total for the set

CL4 is Mondays data for line 4
CM4 is Tuesdays data for line 4
CN4 is Wednesdays data for line 4
CO4 is Thursdays data for line 4
CP4 is Fridays data for line 4

I have been using this formula without alteration (as needed) for in excess of 10 years but yesterday when I went to use it the formula read as (the changed portion of the formula has been bolded).

(CQ4-(CL4+CM4+CN4+CO4+CQ4))

I am the only person using the PC and I have the spreadsheets encrypted. No one other than myself has access to the PC so this cannot be a case of someone accidentally or intentionally changing the formula.

So the question is how can this happen and how do I prevent it? Or rather, how does this happen and is it possible to prevent it (or to know when it happens somehow so I can correct the error?

My concern is not so much with this particular formula because I can see immediately if there is a problem with the formula (when I plug the formulas result into the missing data point the formula must generate an answer of 0, if it does not I would know there is a problem) but rather with the many formulas that are embedded in the spread sheet and which can be considerably more complex than the formula in question. There is no way I would know if one of the many formulas has become corrupt and thus no way for me to know that the output is garbage. Errors in any of these many formulas could be very damaging to my business as decisions regarding buying and selling will depend on the results of the weekly spread sheet analysis.

Any insight and suggested solutions so as to protect against these sorts of errors will be greatly appreciated.

 

It could happen if you did a cut/paste (not a copy/paste) of cp4 to cq4. That would change the reference in your formula. It could also happen if you inserted a column between columns co and cp (or if you inserted a cell between co4 and cp4.

Could you have inadvertently done something like that?

(If you did a cut/paste from cp4 to cq4, and then did a copy/paste from cq4 back to cp4, the formula would still be incorrect.)

 

No, the formula lives at A1. That is, since I need the formula infrequently and I do not want to have to create it each time I need it, I just put it in A1 and thats where it sits until I copy/paste it to where I need it when I need it. Besides I would not have any reason to add or remove columns and even if I did inadvertently do so, since the formula is pasted at the end of the week just before I begin the analysis it should not have an issue of this sort.

Very disconcerting that I may be basing critical decisions on analysis that is corrupt because a formula changed itself.

Does anyone know of a way I could confirm consistency of the spreadsheet? I know there are programs that will show how code has changed from one version to another when program development is being done. Is there something that could look at the guts of a spreadsheet and do the same thing. ---- Of course data should be exempt from this as that will change daily.

 

You store the formula in A1. When you copy/paste, you change the relative cell references in the formula. To what cell do you copy the formula that resides in A1?

What does the formula say prior to copy/paste? What does it say after it has been copied to the target cell?

BTW, as an actuary I have worked with Excel since its creation (and Lotus 123 prior to that). Whenever something inexplicable has happened to cause me to doubt the integrity of the program, it was invariably an issue of human error - mine. Before you start questioning the program, please think carefully about your procedures and what you might have done differently in your current use of the spreadsheet.

 

Disclaimer: I haven't used spreadsheets in many years.

Does that spreadsheet use macros or is it able to ?

One could guess that some macro went wrong, or that a malicious macro (infection!) did the damage.

But my guess is that this is a case of human error. I know, I've worked with complicated spreadsheets, and it's easy to make an error. You mention a 'series of spreadsheets' !

Are you the only one who has access to the spreadsheets ? Perhaps someone else made the error ?

 

I have attached a jpg showing the correct formula as it exists in the A1 cell and in the CR4 cell (I copied and pasted the formula from A1 to CR4).

In the past any time something went wrong I assumed it was due to something I did. This time I cannot imagine what I could have possibly have done to cause the formula to change short of intentionally changing it,,,,,,,which I would not do.

 

Yes, the spread sheet is able to use macros but the formula in question in no way has a macro associated with it. Also, the macros used were created using the programs own internal macro creation/recording function.

I am the only one who has access to the spread sheets.

 

There is something I don't understand here.

Your formula in cell A1 references column IV, which is the last column in excel. If I copy that formula to any column other than A, it will give me an invalid cell reference error because the new reference would be to a column beyond IV (which doesn't exist).

For example...
Let's say I have a formula in cell A1:
= B1 + 5
If, I try to copy that formula to cell IV1, it will attempt to reference cell IW1 which doesn't exist - and I would get an error message.

What am I missing?

 

As I understand it the formula in cell A1 refers to the cells at the end of a previous spreadsheet somehow,,,,that is, as if you were appending spreadsheets. Once you move it to another cell that will allow it to acess cells only in that spreadsheet it will appear as its expected to.

See attached.

 

You're using 123 and not Excel - sorry, I should have noticed that. Apparently, 123 operates differently in this respect - allowing a formula to "wrap around" to beginning of spreadsheet. That is not the case in Excel as you can see from the attached screenshot where I attempt to copy cell A1 to B2 - I get an error message.

I'm temporarily at a loss - will think about this some more. In the meantime, maybe someone else will be able to help.

 

Understood, thank you.

 

Based on what you've outlined above, I don't see why this should have happened. Are there any obscure preferences in 123 that might have caused this? There are preference items in Excel, e.g., to extend data range formats and formulas, but I don't see how they might have caused this.

If you think the program is corrupted, you might consider a reinstall.

I've had instances in the past where (in Excel) I accidentally hit an incorrect sequence of keys and produced totally unexpected results - that might have happened to you. (There are many obscure shortcuts in Excel, and I succeeded in hitting on them by accident!)

Wish I could have been more helpful - if you come up with a definite answer, please post it -I'd like to hear it.

 

I don't know about any obscure preferences that could have cause this and the fact that A1 is only used to store the formula and not a cell that would be actively used otherwise makes it very unlikely that the problem occurred because of something I did. Apart from this issue 123 seems to be working fine but as I said it would be very difficult to know if any other formulas have been compromised. I would switch to EXCEL but there are many macros and graphs that I have created as well as some 8 distinct linked spreadsheets and I am concerned that the transfer would cause serious problems. Its a dilemma since 123 is no longer being developed and is not even Vista compatible let alone compatible with Windows 7.

Thanks for trying to help, much appreciated.