Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 17th 11, 07:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 26
Default 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   Report Post  
Old June 17th 11, 08:11 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 26
Default 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   Report Post  
Old June 17th 11, 08:43 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 26
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculations from tab to tab not automatically updating Leesa Excel Worksheet Functions 4 July 15th 09 02:58 PM
Updating Closed External Refs [email protected] Excel Discussion (Misc queries) 5 May 7th 07 05:16 PM
Why not automatically updating calculations? walkingmac Excel Discussion (Misc queries) 5 March 10th 06 03:00 AM
What if formulas and calculations - not true/false BJS New Users to Excel 2 June 14th 05 03:17 PM
Updating calculations + graph when i enter in new data WITHOUT OFF superman Excel Worksheet Functions 0 February 2nd 05 05:05 PM


All times are GMT +1. The time now is 11:58 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017