Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel false positives for circular refs and not updating calculations
I created an Excel 2007 workbook w/ hundreds of formulas and large
enough amounts of data to require using manual calculations turned on to edit at all. At some point during my development I noticed Excel didn't seem to completely calculate everything when asked, and turning to Automatic calc doesn't fix it. It doesn't crash, and even finishes w/n 5 minutes or so when calculating. But it clearly doesn't update everything. I can tell this by going into a cell's formula bar and pressing enter, which does update the value (in most cases, sometimes it doesn't). I notice I'm also getting circular references warnings where they clearly do not exist, for instance, for cells that refer to other cells that do not refer back to them. Has anyone had such an issue? I'm use to Excel crashing when I push it too hard, but this is completely new and much more dangerous. I can't feel certain about any of my calculations! BTW, I typically do split up my work in smaller Excel files, but in this instance, it will cost me dearly to have to split any of it up. Thanks for any help, K |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel false positives for circular refs and not updating calculations
As an update, I see where the problems start, which is in Col P of a
given sheet (A thru O are fine), about 615 rows down. This is meaningful b/c I used some test data to develop the xlsx file that was this many rows down. To prepare for the largest data sets I'll be inputting once the workbook functions properly, I later made it so all formulas are present to row 6000 and only return a value if the data I input goes that far down. But there's no reason for it not to update that I know of. The formula for each cell (from row 2 to 6000) in Col P is {=MAX(IF(K $1:K$6000=K467,O$1:O$6000,""))}, where this example is the formula for cell P467. K467 in the formula would be K468 at cell P468. This formula is obviously an array formula but is a different array formula for each single cell in Col P (that is, I only have one cell selected when I press Ctrl-Shift-Enter). I can only assume it's just too large for Excel to handle 6000 different arrays in one column, even though the formula itself is pretty simple. Anyway, this was my attempt at getting something like a MAXIF function. If anyone has any better ideas I'd appreciate it! Thanks, K |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel false positives for circular refs and not updating calculations
OK, so I now see what's going on.
the arrays in Col P don't like the columns they reference due to the 'blank but not really blank cells. For instance, Col O at O5379 is =IF(ISNUMBER(K5379),SQRT(L5379^2+M5379^2+N5379^2), ""). It's that "" that I used to return blank cells that messes it up. Anyone know of a way to avoid using "" and still keep cells blank (hopefully w/o using VBA since I never get it working right)? Sorry to keep updating, but I keep thinking of ways to get at it after I post. Thanks again, K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations from tab to tab not automatically updating | Excel Worksheet Functions | |||
Updating Closed External Refs | Excel Discussion (Misc queries) | |||
Why not automatically updating calculations? | Excel Discussion (Misc queries) | |||
What if formulas and calculations - not true/false | New Users to Excel | |||
Updating calculations + graph when i enter in new data WITHOUT OFF | Excel Worksheet Functions |