 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007I can't get the formula result to display on my spreadsheet.
This is a phenomenonally simple task: All I want is the formula "=SUM(B38:F38)" result to display on my spreadsheet. Excel shows $0.00 in the cell. The formula result, however, is '$9288.72.' I am so frustrated!!! Thanks for any help with this prob. Sounds like your numbers are stored as text, format them to a number format.  Signature-John Northwest11 Please rate when your question is answered to help us and others know what is helpful. > This is a phenomenonally simple task: > [quoted text clipped - 3 lines] > > Thanks for any help with this prob. > Sounds like your numbers are stored as text, format them to a number format. If its the case of text numbers, think re-formatting in itself doesn't change the underlying values. We need to copy an empty cell, then select the source range, eg: B38:F38, right-click > paste special > add > ok. This should convert all text numbers within the source range to real numbers, and the SUM will work. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Likely that calc mode was inadvertently set to Manual here. Press F9, does it compute properly? To check/change calc mode, click Tools > Options > Calculation tab. Ensure "Automatic" is checked > OK. Along similar vein, another non-computing phenomena to watch out for is the possibility is the cell with the formula might have been pre-formatted as text, unknown to you of course, when you dropped the formula in. In such an instance, just re-format the cell as either general or number (via Format > Cells), then re-enter the formula by clicking inside the formula bar and pressing ENTER. Note that re-formatting alone does not trigger calculation, you need to re-enter the formula. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > This is a phenomenonally simple task: > [quoted text clipped - 3 lines] > > Thanks for any help with this prob. Hi Max & John, I'd done all you suggested and checked again. Still, I get $0.00 in the cell and the formula result remains in the formula box. :( I have the cell formatted to 'currency.' All my other cells and calcs work fine. Kai San Diego > Likely that calc mode was inadvertently set to Manual here. Press F9, does it > compute properly? To check/change calc mode, click Tools > Options > [quoted text clipped - 20 lines] > > > > Thanks for any help with this prob. Quite a mystery then <g>, given that the usual checks didn't resolve it .. Perhaps you could use either of the 2 free filehosts listed below to upload your sample book and then post the link to it in response here (the link is generated when you upload, just copy and paste it here) http://www.flypicture.com/ http://cjoint.com/index.php For cjoint.com (it's in French), just click the "Browse" button, navigate to folder > select the file > Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. Kindly note that no attachments should be posted *directly* to the newsgroup. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi Max & John, > [quoted text clipped - 6 lines] > Kai > San Diego Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= The cells in question are C41 and D44. It actually seems like that entire quadrant is involved. As previously noted, when I click the 'fx' icon and get the 'function arguments' box, the result is there. It(the result) just won't post in my designated cell. You may note that another cell in the 38 row works. Thanks for your time and thoughts. k > Quite a mystery then <g>, given that the usual checks didn't resolve it .. > [quoted text clipped - 21 lines] > > Kai > > San Diego > Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= > The cells in question are C41 and D44. It actually seems like that entire [quoted text clipped - 32 lines] >>> Kai >>> San Diego Try this in c41 instead of SUM =(B38+F38) >> Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= >> The cells in question are C41 and D44. It actually seems like that [quoted text clipped - 38 lines] > > =(B38+F38) Please ignore..I wasn't looking at it close enough. D38 contains a circular reference, change formula to: =SUM(D5:D37) George Gee > Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= > The cells in question are C41 and D44. It actually seems like that entire [quoted text clipped - 43 lines] >> > Kai >> > San Diego Many, many thanks! Sorry about the = sign in the hyperlink. S > D38 contains a circular reference, change formula to: > =SUM(D5:D37) [quoted text clipped - 48 lines] > >> > Kai > >> > San Diego The hyperlink is not complete, it needs the = sign on the end! George Gee > Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= > The cells in question are C41 and D44. It actually seems like that entire [quoted text clipped - 43 lines] >> > Kai >> > San Diego It looks like the circular reference error in D38 (one of the precedent cells) is causing the problem here, as observed by George. If you check the Iterations box (in Tools > Options > Calculation tab), it'll work as-is (tested here). It's best of course to correct the circular error in D38's formula as George has pointed out. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Here's the link to my WS: http://www.flypicture.com/download/NzM5Mzc= > The cells in are C41 and D44. It actually seems like that entire [quoted text clipped - 8 lines] > > k Thanks for all you time Max. I'm relieved. Amazing, phenomenally simple problem. Annoyed I didn't figure it out on my own. S > It looks like the circular reference error in D38 (one of the precedent > cells) is causing the problem here, as observed by George. [quoted text clipped - 14 lines] > > > > k No prob. Glad it was nailed down here <g>. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thanks for all you time Max. I'm relieved. Amazing, phenomenally simple > problem. Annoyed I didn't figure it out on my own. > > S Here's my standard list SignatureKind regards, Niek Otten Microsoft MVP - Excel ================================================================= Your Numbers don't behave (like numbers) Niek Otten, May 11, 2006 Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In short: Your Numbers look like Numbers, but they really are Text. Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's ISNUMBER() function to check your cells; maybe you solved your problem in the first step! · Format an empty cell as Number. Enter the number 1 in it. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your cells are "real" Numbers now · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function to remove them · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to remove most of them · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL() function to remove them. It can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================================= | Hi Max & John, | [quoted text clipped - 31 lines] | > > | > > Thanks for any help with this prob.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.