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

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

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