If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
AVERAGE of row gives no result
On Sun, 2 May 2004 18:11:50 -0500, turtleman2
wrote: TO Peo: I did as your post suggested, highlighted the "COUNTA~7" portion of the formula, and it returned TRUE. Then I changed all the formulas in row 50 to simple sums, and tried to get an average. Same thing: No result at all. TO Ron Rosenfeld: I did as your post suggested, highlighted the [no values displayed] cell which contained the AVERAGE formula, and placed the cursor in the formula bar, hit F9, and... It returned the correct answer! Whoooeeee! At first it returned an answer w/ 11 decimal places, even though that cell was formatted for 'Number, 2 decimal places'. Then I hit Enter again, and it was reduced to 2 places....Also, I went to 'FormatConditional formatting', and it said: "Preview of format to use when condition is true: NO FORMAT SET" So what the heck does this all mean? Another thing [besides the fact that F9 caused it to work] that confuses me is: When I select the cell that now displays the correct Average value, up in the formula bar, where I'd expect to see the Average formula displayed, there is simply the answer-number/value with 11 decimal places. The 'average formula' that was used has disappeared. Ron, thanks a bunch for your input [:-) BUT can you please tell me what you think was the cause of my problem? Thanks again to all the posters for time invested! OK -- When you highlighted the cell and F9 you saw the correct result in the formula bar. Unfortunately, when you enter, what you see replaces the formula. To exit 'gracefully', use esc rather than enter and the formula will remain. The behavior with regard to the decimal places is expected. Now as to why you cannot see the result. Let's check something else with the format. Format/Cells/Font Color: What do you see? Check to be sure it says automatic and that the font is not formatted to the same color as the background. riprap. --- Message posted from http://www.ExcelForum.com/ --ron |
#12
|
|||
|
|||
AVERAGE of row gives no result
On Sun, 2 May 2004 20:24:48 -0500, turtleman2
wrote: to Ron Rosenfeld, On 'Font color of problem cell': I checked that several times during this little trial, but it was set to Automatic. I wish I understood what-all happened, but I do not. Check this out: After changing the ToolsOptionsCalculation setting frm 'Automatic' to 'Manual', and getting the average function to work, I went back ['cause research is always interesting!], re-set Calculation to 'Automatic' [the original setting]==and now the blessed thing WORKS THE WAY IT'S %^#?@*SUPPOSED TO ! Dang I hate when that happens! In other words, even though that particular worksheet is working O.K. at the moment, I have no clear idea of what the problem what in the first place. Feel free to launch any suggestions you've got, Ron. riprap. Well, it seems to me that the function was always working (from what you've written -- you were able to see both the formula and the result F9 in the formula bar). It's just that the answer was not being displayed in the cell. Other than formatting (certain number formats, conditional formatting, or setting font color to white); or selecting to not display zero values (and having a zero value as the result), I don't know what else would cause your results. So I don't really understand why cycling to manual and then back to automatic calculation would change any of those parameters. What did you do to check that there were no macros running? One place to check for event macros (which might do something like what you describe), is to right click on the worksheet tab; and then select View Code... --ron |
#13
|
|||
|
|||
AVERAGE of row gives no result
On Mon, 3 May 2004 09:29:28 -0500, turtleman2
wrote: to Ron Rosenfeld et al, I appreciate your time & effort. It turns out that, every time the Average function is input into that worksheet, whether or not it works is dependent on the ToolsOptionsCalculation setting.... If that setting is on Automatic, I've got to enter the formula as usual, click Enter, then [having seen NO RESULT in the input cell] go back, re-select input cell, place cursor in formula bar & click on F9, and the answer comes up...But if the setting is on Manual, the input function gives the correct Average first time every time. I've tested it 15+ times, in different areas of sheet, and am convinced that's the 'answer.' Now, if I could only figure out WHY that setting makes the difference, g, but I'm still working on that! Thanks again, & have an excellent Day. turtleman. --- If you'd like, email me a copy of the worksheet. Just reverse this address to validate it: ten.aidaca@dlefnesornor --ron |
|
Thread Tools | |
Display Modes | |
|
|