AVERAGE of row gives no result

  • Thread starter Thread starter turtleman2
  • Start date Start date
T

turtleman2

This problem MUST have a really simple answer; I just don't know what it
is...
When I attempt to average the values in one particular row on my
worksheet, there is never any result given! The 'average' function
works everywhere else in the sheet (on other rows and columns, etc) but
in this one row, after entering the correct syntax (=average(range)),
then clicking on Enter, the cursor moves on but leaves no result behind
(??????)
POSSIBLE CLUE: The values in the 'problem row' are all the result of an
"IF(SUM)"-type formula.
Any help on this would be appreciated [:-) I've never had any problem
before with 'average' worksheet function, so this has me kind of
weirded-out!

riprap.
 
No--I checked that. The entire row's formatted as 'Number', with
decimal places.
Any other ideas
 
When I attempt to average the values in one particular row on my
worksheet, there is never any result given!
Click to expand...

What does that mean?

Is the cell blank?
Does the cell display the formula?
Does the cell show a 0?

If the cell appears blank, what is the format of the cell? And exactly what is
your formula within the cell.


--ron
 
Just because it is formatted as number does not mean it is a number.

Copy an empty cell which is formatted as number or general.

Select the row and Paste Special>Add>OK>Esc.

Try your AVERAGE again.

Gord Dibben Excel MVP
 
Is the formula still in the cell?

If not, you may have an event macro deleting the formula.

If so, check for conditional formatting that would change the font color.

Do other fuctions (e.g., =SUM(rng) ) work?
 
TO Ron Rosenfeld:
When I say "there is never any result given", I mean that after
click on Enter [to execute the Average function] and the cursor move
on, the cell containing the Average appears blank. No zero, no erro
msge, no anything. If I go back to that cell and select it, my Averag
formula appears up on the Formula bar just fine, but there's no resul
in the cell.

TO JE McGimpsey:
YES the formula is "still in the cell".
But there are no macros in that workbook.
ALSO: I tried a simple "=SUM[range]" w/ in that row, and nothin
shows up for that either (!).

TO Gord Dibben:
I tried your "Paste special>Add>OK>Esc"...no joy.

TO ALL: I went back and made sure the entire sheet (except for heade
rows) is formatted as 'Number', w/ 2 decimal places. AND I experimente
with adding & averaging ranges in other areas of sheet. No problems.

The formula which yielded the values of the row in question is:
=IF((COUNTA(AM$3:AM$48)>=7),SUM(AM$3:AM$48),"")
It has been applied to the range B50:AO50.

Any other ideas?

riprap
 
If you select the cell with the formula, then in the formula bar use the
mouse to highlight this part

COUNTA(AM$3:AM$48)>=7

then press F9, what do you get? If you get FALSE the formula returns the
correct result,

Try just using this

=SUM(AM$3:AM$48)

do you get a result now?
 
TO Ron Rosenfeld:
When I say "there is never any result given", I mean that after I
click on Enter [to execute the Average function] and the cursor moves
on, the cell containing the Average appears blank. No zero, no error
msge, no anything. If I go back to that cell and select it, my Average
formula appears up on the Formula bar just fine, but there's no result
in the cell.
Click to expand...

Check these three things:

Select the cell with the AVERAGE formula that shows blank.
Put your cursor in the formula bar and <F9>. What shows up?

Then select (from the menu bar): Format/Conditional Formatting
Is there anything set?

Select Tools/Options/View Window Options
Is "Zero Values" checked?


--ron
 
Ron Rosenfeld said:
TO Ron Rosenfeld:
When I say "there is never any result given", I mean that after I
click on Enter [to execute the Average function] and the cursor moves
on, the cell containing the Average appears blank. No zero, no error
msge, no anything. If I go back to that cell and select it, my Average
formula appears up on the Formula bar just fine, but there's no result
in the cell.
Click to expand...

Check these three things:

Select the cell with the AVERAGE formula that shows blank.
Put your cursor in the formula bar and <F9>. What shows up?

Then select (from the menu bar): Format/Conditional Formatting
Is there anything set?

Select Tools/Options/View Window Options
Is "Zero Values" checked?
Click to expand...


Hi Ron,

I also thought about the zero values, that could explain things, all values
are
text and thus the formula returns zero and result won't be displayed
If this is numbers imported from the web and there are trailing char(160)
then the coercion using adding
zero or multiplying with 1 won't work
 
TO Peo:
I did as your post suggested, highlighted the "COUNTA~7" portion o
the formula, and it returned TRUE.
Then I changed all the formulas in row 50 to simple sums, and trie
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 th
formula bar, hit F9, and...
It returned the correct answer! Whoooeeee! At first it returned a
answer w/ 11 decimal places, even though that cell was formatted fo
'Number, 2 decimal places'. Then I hit Enter again, and it was reduce
to 2 places....Also, I went to 'Format>Conditional formatting', and i
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 fac
that F9 caused it to work] that confuses me is: When I select the cel
that now displays the correct Average value, up in the formula bar
where I'd expect to see the Average formula displayed, there is simpl
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 m
what you think was the cause of my problem?
Thanks again to all the posters for time invested!

riprap
 
Discovery:
I've found out that, if I go to Tools>Options>Calculation, and chang
the setting under Calculation from "Automatic" to "Manual", w
"Recalculate before save" checked, that now the average formula work
perfectly.

But that's the first time I've ever had to change that setting.
Why does that setting being changed cause things to work?

Thanks again for your time [:-)

riprap
 
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 'Format>Conditional 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!
Click to expand...


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.
Click to expand...

--ron
 
to Ron Rosenfeld,

On 'Font color of problem cell': I checked that several times durin
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 Tools>Options>Calculation setting frm 'Automatic' t
'Manual', and getting the average function to work, I went back ['caus
research is always interesting!], re-set Calculation to 'Automatic
[the original setting]==and now the blessed thing WORKS THE WAY IT'
%^#?@*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 th
first place.

Feel free to launch any suggestions you've got, Ron.

riprap
 
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 Tools>Options>Calculation 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.
Click to expand...

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
 
to Ron Rosenfeld et al,
I appreciate your time & effort. It turns out that, every time th
Average function is input into that worksheet, whether or not it work
is dependent on the Tools>Options>Calculation setting....
If that setting is on Automatic, I've got to enter the formula a
usual, click Enter, then [having seen NO RESULT in the input cell] g
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 inpu
function gives the correct Average first time every time. I've teste
it 15+ times, in different areas of sheet, and am convinced that's th
'answer.'
Now, if I could only figure out WHY that setting makes th
difference, <g>, but I'm still working on that!
Thanks again, & have an excellent Day.

turtleman
 
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 Tools>Options>Calculation 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.


---
Click to expand...

If you'd like, email me a copy of the worksheet.

Just reverse this address to validate it: ten.aidaca@dlefnesornor




--ron
 
Back
Top