Works spreadsheet cells not calculating?

This is a bit esoteric, so very grateful in advance for any help offered.

I've got a big works spreadsheet where I record all my trades, P&L etc. All the calculations are very simple, of the ((buy price - sell price) * units bought - comms) type argument. But I noticed the other day that sometimes the calculations 'stick'. That is, if I update a price, the calculated P+L cell doesn't change. The only way to get it to work again is to copy or retype the formula into the cell. It doesn't happen often, but I've noticed it three or four times. How many have I not noticed that are now corrupting the calculation of my totals?

I use this spreadsheet to calculate my tax liabilities, work out how much I'm making, the lot. Now I'm worried because some rogue cells might be throwing the whole lot out. Other than going through it line by line (and in which case I might as well use a bloody paper ledger), does anyone have any tips on how I can either force a proper recalculation, or check which cells are stuck? Just recopying the formulas isn't as simple as it sounds, because I've got all sorts of different instruments in there, which all have different constants. Needless to say, the Microsoft support website had nothing on this...

Thanks in advance.

Take a look at Tools / Options and go to the Calculation tab. I have Automatic checked - perhaps you are on Manual? There's also a Calc Now button there, though I've never had to use it.

Thanks for quick reply, but no it's not that I'm afraid.

I'm stumped then, soz. Never come across this problem. Hopefully some other excel nerd will be along to help you soon.

I am an Excel nerd but Jack is not talking about Excel. I suspect the advice about checking the calculation mode is valid, although I do not know how you check / change it in Works. Still worth looking at though.

Jack,

If you trade options, the problem may be a sticky strike or sticky delta.

Grant.

swandro said:

Jack is not talking about Excel.

Click to expand...

Oh yeah, oops

swandro said:

I suspect the advice about checking the calculation mode is valid, although I do not know how you check / change it in Works.

Click to expand...

Tools / Options / Data Entry / Use Manual Calculation.

Thanks to all - but it's none of those... I've a horrible feeling it's some kind of bug either in my PC or the software and there's not a lot I'm going to be able to do about it. As I say, it doesn't happen often, but often enough that it's unnerving...

I only know excel not works but here's some ideas. You could copy then paste your whole area where you have formulas into a completely new (excel/works) file. In excel (should be same in works?) this won't take any overall settings into the new file if that is where the corruption is. It may be worth only pasting formulas too rather than all as the corruption may be in the formatting of individual cells.

If that doesn't work I'd copy paste into a new sheet all the bits that work OK (excl formats) then retype in the cell that isn't behaving and copy this formula into the cells where it should be. If it still is misbehaving try some subtle changes to the formula and see if that recalcs OK. When you do this do it in a new cell though.

This issue sounds like a program error rather than malicious bugs to me though. I think I had something similar in Excel in the past (possibly in an older version of Excel).

You could easily have what's known as a "circular reference" somewhere. NExt time this happens go through every sheet in the workbook and look for a cell address apprearing right at the bottom on the grey bar just to the left of "Ready"

CTRL-ALT-F9 will force a recalculation of everything in that session of Excell.

Cut and paste this into a macro and run it to rebuild all calculation trees . . .

Sub ReallyRecalcAll()

Application.CalculateFullRebuild

End Sub

Jack o'Clubs said:

Thanks to all - but it's none of those... I've a horrible feeling it's some kind of bug either in my PC or the software and there's not a lot I'm going to be able to do about it. As I say, it doesn't happen often, but often enough that it's unnerving...

Click to expand...

I am slightly off the thread, but while we have a gathering of spreadsheet experts can I ask a simple question please. I have set up a DDE feed from my system into Excel successfully, but I can only populate the same cell and each tick overwrites it. Can anyone tell me how to move the data on so it fills the following cells and I can build a data file. Many thanks in advance.

Charles

Charles, you'll need to do some VBA coding behind your spreadsheet in order to do that.

If you want to know where the code goes, try this first on a blank worksheet :

Go to view/toolbars and tick Visual Basic.
Toolbar pops up
Click on the visual basic editor icon
Another window pops up, this is where your code goes
To get back to normal excel mode, click on exit design mode on the first toolbar that you brought up. Close the code window.

Now there's just the little matter of coding ! If you have loads of data coming in you might do better to write it to a seperate text file. If I have time later I'll post again

rog1111

Crawsthorne said:

I am slightly off the thread, but while we have a gathering of spreadsheet experts can I ask a simple question please. I have set up a DDE feed from my system into Excel successfully, but I can only populate the same cell and each tick overwrites it. Can anyone tell me how to move the data on so it fills the following cells and I can build a data file. Many thanks in advance.

Charles

Click to expand...

rog1111 said:

Charles, you'll need to do some VBA coding behind your spreadsheet in order to do that.

If you want to know where the code goes, try this first on a blank worksheet :

Go to view/toolbars and tick Visual Basic.
Toolbar pops up
Click on the visual basic editor icon
Another window pops up, this is where your code goes
To get back to normal excel mode, click on exit design mode on the first toolbar that you brought up. Close the code window.

Now there's just the little matter of coding ! If you have loads of data coming in you might do better to write it to a seperate text file. If I have time later I'll post again

rog1111

Click to expand...

Thanks very much. I was just trying to get a quick data file for testing, but I will probably take up your suggestion of building a text file as my final system will be in C#

Thanks again Charles

Crawsthorne said:

. . . I have set up a DDE feed from my system into Excel successfully, but I can only populate the same cell and each tick overwrites it. Can anyone tell me how to move the data on so it fills the following cells and I can build a data file. . . .

Click to expand...

You can't, DDE doesn't fire off an "event" that can be captured + DDE data comes in independently of any code running.

You need to code using an api (would have thought your software vendor would have one)

The "kludge" would be to use Excel's "OnTime" event to fire off a trivial macro every x seconds that examines the current value of the DDE cell and comparing it with the last value (hint : use trhe "Static" declaration of your variable vLastValue)
Something like this (I havn't run it to check) . . .

Option Explicit
'
'========================================================
'
Sub StartPriceCapture()

'Run SubCheckValue every 15 seconds
Application.OnTime Now + TimeValue("00:00:15"), "CheckValue"

End Sub
'
'========================================================
'
Sub CheckValue()

Static LastPrice As Variant
Dim ThisPrice As Variant
Dim TempCell As Range

ThisPrice = Range("PriceCell").Value

If ThisPrice <> LastPrice Then

'move to first blank cell at bottom of column.
'You would need to Errorcheck here to make sure there's not more
'than 64,000 rows etc
Set TempCell = Range(TopOfPriceColumn).End(xlDown).Cells(2, 1)

With TempCell
.Value = ThisPrice
.Cells(1, 2).Vaue = Now 'time stampcell to right
End With

End If

LastPrice = ThisPrice

End Sub
'
'========================================================
'

Actually, one of my collegues suggested using DDE.Poke, but I havn't used that since the old king died & Google wasn't much help.

Having said all that, it may be worth having a nose around the Interactive Broker forums.

I have no problem using

sub Worksheet_Calculate()

which is fired every time any cell on the worksheet updates via DDE

(using IB)

rog1111

rog1111 said:

I have no problem using

sub Worksheet_Calculate()

which is fired every time any cell on the worksheet updates via DDE

(using IB)

rog1111

Click to expand...

hmmmm, yup, that looks like it would work tho' you'd obviously have to have a second cell refernecing the DDE cell.

In this case I find that the DDE linked cell(s) are sufficient, although no harm done by using another cell if required.

rog1111

A Dashing Blade said:

hmmmm, yup, that looks like it would work tho' you'd obviously have to have a second cell refernecing the DDE cell.

Click to expand...

rog1111 said:

In this case I find that the DDE linked cell(s) are sufficient, although no harm done by using another cell if required.

rog1111

Click to expand...

Thank you all very much for your help and I am sorry to have "borrowed" the thread.

Charles

Works spreadsheet cells not calculating

Check out: -

Calculations may not occur in an Excel workbook that has many formulas when you use the Calculate method to calculate formulas or after you press SHIFT+F9 to calculate formulas

The problem occurs if you have two excel spreadsheets that use DDE. Shift-F9 on one, switch back to the other, DDE stops updating. Occurs with the MDI interface.

There is a work-around. Customers need to be educated aswell.

Ian,

This isn't a problem with Excel 2007. Simultaneously I'm running two sheets both comprised of dde's, formulae and vba, one on a tick-by-tick basis, the second on a 1-minute basis. However, the amount of formulae is small.

I think the major problem is where one has many dde's and a stack of formulae which recalculate whenever a cell changes.

Grant.