I have a Windows 7 Excel spreadsheet with 10 worksheets that feed into a consolidating workshet. Each of the 10 worksheets uses the same design and formulas. Seven of the worksheets feed fine. Three feed the correct info for a particular line items (which is itself part of a calculation). The resulting calculation is incorrect - not by much, but incorrect none-the-less. If I type in the same number in the cell (vs. using the import from the worksheet) that is picking up the number from the worksheet (which simply uses the "=" then clicking on the cell in the worksheet I want to import info from) I get the correct answer. It's not a rounding error because the variance is too large to account for rounding. Is this some sort of bug or is there a way to avoid this error???

I would need to see the sheets to be able to debug them. I use Excel every day in my shop. Can you post them on Skydrive? Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Hardcore Games | Vegan Advocate | PC Reviews

Thanks for your offer - - however - - I'd prefer not posting the worksheet. If you're still interested in helping me solve the problems consider this: 1. the three cells in question - or of 10 (7 don't have this problem) - pick up a number from a worksheet (in the same spreadsheet). 2. each of the worksheets is constructed with the same design and formulas 3. the numbers being picked up feed into a simple calc 4. the calc - in 3 of 10 instances - results in an incorrect answer 5. If I type in the same number that's being picked up from the worksheet - I get the correct answer. 6. So, it seems to me the problem is some sort of a bug, likely in the main worksheet that all the sub worksheets roll up to. Sorry about not feeling comfortable with providing the worksheet.

what is the formula being usedWindows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

There are two worksheets in question: 1. All entries are made to ancillary worksheets ("Ancillary Worksheet" or "Ancillary Worksheets"). Each Ancillary Worksheet is identically designed from layout to all formula. 2. Certain totals from the Ancillary Worksheets consolidate into a main worksheet (the "Main Worksheet") which summarizes various investment. This is the layout of the Ancillary Worksheet(s): Column D E F G H I J K L M Quantity Date Acquired Cost Per Share Total Cost Market Value Short-Term Gain/Loss Long-Term Gain/Loss Total Gain/Loss Weighted Avg Share Price Shares Date Price p/sh Amt - - - - Weight (Concentration) Weight x Price p/sh 13,574.6610 5/27/11 $ 11.05 $ 150,000.00 $ 150,000.00 74.51% $ 8.23 1.3610 6/2/11 $ 11.04 $ 15.03 $ 15.04 0.01% $ 0.00 4,541.3260 6/21/11 $ 11.01 $ 50,000.00 $ 50,181.65 24.84% $ 2.73 36.9920 7/5/11 $ 11.04 $ 408.39 $ 408.76 0.20% $ 0.02 41.868 8/2/2011 $ 11.04 $ 462.22 $ 460.97 0.23% $ 0.03 39.3380 9/2/11 $ 11.04 $ 434.29 $ 431.14 0.22% $ 0.02 0.00% $ - 0.00% $ - 0.00% $ - 0.00% $ - 18,235.5460 $ 201,319.93 $ 201,497.56 100.00% $ 11.04 The formula in Col L is disigned to get the weighting of each individual investment (based on Total Cost). The actual formula is: =IF(G6=0,0,G6/$G$16) where G6 is Total Cost of the investment and G16 is the Total Cost of all investments. This calc give a weight for each individual investment and is always less than 1 and is experssed as a %. The formula in Col M is designed to apply the weighting in Col L to the Cost Per Share (Col F). The actual formula is: =L6*F6 where L6 is the weighting calc'd above and F6 is the Cost Per Share of an individual investment. The total of Col M is the Weighted Average Cost of the individual purchases. The Main Worksheet is layed out as follows: Column B C D E F G H I J K $ Investment Basis Last Trans Dt Total Shares Avg Sh Pr (Basis) -1) Today Sh Price Today Value In (Out) of the Money Today Val In (Out) of the Money Avg Sh Pr Type of Invest Symbol 5/27/11 6,839.3200 75,000 11.04 10.70 73,181 (1,819) (2,303) Columns E, F, G, I, J and K populate from info in the Ancillary Worksheets. Columns B, C, D and H are manual inputs. Column G is the one I'm having probles with --- or, possibly, Col K. The formula in Col G is: =[worksheet name]!M16 (note: I omitted the actual name of the worksheet). So, this calc picks up the weighted cost per share from the Ancillary Worksheet. The formula is Col K is: =IF(I7=0,0,(E7*H7)-(E7*G7)) where E7 is Total Shares of an indivdual investment and H7 is the actual share price at the end of the trading day - and - E7 is Total Shares of an indivdual investment and G7 is the weighted cost per share calc'd in the Ancillary Worksheet - when this latter amount is subtracted from the former calc the result is the In (Out of the Money position (based on the) Average Share Price. Not sure where the problem is. The amt in Col G is correct but when it runs through the formula in Col K I get an incorrect answer. However, if I manually input the same amount in Col G (vs having it "imported", if that's the correct term using the =[spreadsheet name]!cell name convention) I get the correct answer in Col K. So, the column K formula is correct (and, it works just fine on 7 of the 10 Ancillary Worksheets). Seems the problem has something to do with the "import" of Weighted Avg Share Price from the Ancillary Worksheet in Col G.

Probable a problem with your weighted average error propagation in excel can be tricky to track down I took econ in school so I can easily manage with financial forumla Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

any ideas??

formula for the weighted average Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

it's a simple formula - - the calc is correct - - seems that the formula in the Main Worksheet doesn't calc correctly using the imported number - - it does calc correctly using a hard copy number. that's not a weighted avg formula issue, is it?

try paste special and see if that can fix the problem Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

See if "Enable Iterative Calculations" under File | Options is turned on. If not turn that off and see if that helps you. Also, make sure the circular reference is not enabled too. for 2010 it should be under the Formula Ribbon and it is under Error Checking. Make sure this is turned off too.

I did not see anything there that looked iterative generally that is used with linear algebra for solving economic order qantities etc look at linear programming Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

right... which is why he needs to make sure its not enabled...

it's not enabled - - I tried w/ enabled and w/o (which was original setting) - - neither one made a difference

not to worry I can help the user with his application here Windows MVP 2010-11, XP, Vista, 7. Expanding into Windows Server 2008 R2, SQL Server, SharePoint, Cloud, Virtualization etc. etc. Hardcore Games, Legendary is the only Way to Play Developer | Windows IT | Chess | Economics | Vegan Advocate | PC Reviews

Not sure where the problem is. The amt in Col G is correct but when it runs through the formula in Col K I get an incorrect answer. However, if I manually input the same amount in Col G (vs having it "imported", if that's the correct term using the =[spreadsheet name]!cell name convention) I get the correct answer in Col K. So, the column K formula is correct (and, it works just fine on 7 of the 10 Ancillary Worksheets). Seems the problem has something to do with the "import" of Weighted Avg Share Price from the Ancillary Worksheet in Col G. I think that this is where the problem lies. The value in column G might look correct, but is it actually something like 11.035 or 11.044 instead of 11.04, which is what you get with the range 11.035-11.044 rounded to 2 decimal places? In other words, is the value shown in column G just a rounded value of what is actually there?

I've had another look at this, and I think the problem can be resolved by making a slight change to the formula used to transfer the data from the auxiliary worksheet to the main worksheet. At the moment, you have the following formula for Column G on the main worksheet: =[worksheetname]!M16 This transfers the data over from the auxiliary worksheet to the main worksheet as intended. However, even though you have Colum G set to show 2 decimal places (dp), it does not mean that the value of the number stored there is actually limited to 2 dp. To overcome this, change the formula so that it reads the following instead: =FIXED([worksheetname]!M16,2) This will do the same as the first formula, only this time the value stored is strictly limited to 2 dp.

Hi, This forum is use to discuss miscellaneous issues that cannot be covered in any other Windows 7 forum. Please understand you problem is an Office related issue. You may also post this question to Microsoft Office forum to get more information about Excel formula: Microsoft Office for IT Professionals Category The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Have we managed to solve your problem yet ?