AVERAGE function gives no result

To roos01:
   Thanks for your time.
   "Tools>options>calculations" is set to "Automatic." It's been set like that for many months, & this is the first time I've ever had this problem.
   Version: Excel 2002.
   The really strange thing is, I can choose any other group of cells on that same worksheet, and the Average function works just fine !
   That's why I'm thinking it's got something to do w/ the fact that the values I'm having trouble with were all derived from an "IF(SUM)"-type formula.

  Any other ideas?

riprap.

I see in your question that your using brackets in your formula? I asume that you ment ( like: =averag(A1:D1)

or even =AVERAGE(C2:C6)  !!

TO roos01:
  You are correct in your assumption; I AM using standard () parentheses when I try to average values in row 50. And I get no result.
  I've tried averaging other cell ranges (columns & rows, both) in that same worksheet; no problems whatever.
  That's why I'm thinking it's got something to do w/ the fact that: All values in the row in question were derived from this formula:
     =IF((COUNTA(AO$3:AO$48)>=7),SUM(AO$3:AO$48),"")
  And that formula was applied to all cells in range B50:AO50. (btw, the formula seemed to execute w/ no problems)

Any other ideas?

riprap.

is it possible that your cell format where you are trying to get your average is not formated to a number?
happened to me before
Russ

When things are weird, try to simplify.  Copy the spreadsheet, then delete  columns c:an.  Next delete rows 4 to 47.
If the problem continues, at least you'll be able to see everything without scrolling.

If the problem disappears, start over and only do one of the deletes.  Next start over until you isolate what is causing the problem,

I've used this binary search method many times and it usually bears fruit fairly quickly.

is your fail formula =average(a50:ao50)?

If so does average(a50) also fail?

If so does =a50 also fail?

to RussWest
  Thanks for your input, but yes the cell in question is indeed formatted for 'Number, 2 decimal places.'

to rberke
   Thank you for the time. I tried the simplified formula, just asking for the average of 1 cell in row 50; no apparent result. In other words, no answer displayed, but when I select that cell, the formula is displayed up above in the formula bar.
   
  Here's the deal: I've gotten some help from www.exceltip.com/, and I've gotten to the point where I input the 'Average' formula, then click on Enter [which as I said, gives no apparent result]. Then, I make sure the cell containing the formula is selected, place the cursor in the formula bar, click on F9, and guess what? The answer [with way too many decimal places displayed] appears in the cell. I click Enter again, and the decimal places reduce to the correct 2 places. BUT even though I can get the answer to finally display in the cell, when I go back and re-select that cell, up in the formula bar the original 'average calculation' has disappeared.

   Can anyone tell me what the sam hill is going on?
 
riprap.

Another Note,
  While I was having the problems with the 'Average' formula yielding no result, in Tools>Options>Calculation I had "Calculation" set to "Automatic." [I've always had that setting in that mode, with all the xl workbooks I've used.]
   But I found out that, If I set Calculation to "Manual", with "Recalculate before save" checked. And guess what? Now the Average formula works perfectly.(???)

  Can anyone shed some light on why this happened? That is, give some explanation that an obvious novice like myself could understand? I'd sure appreciate it!

riprap.

hi riprap1951,

1. is your "if" formula producing result? if not, your values where the if statement is getting the value is not set to numbers. they might be in in text.... make sure they are set in number and try retyping atleast 7 cells, it might solve the problem....this helped me a couple of times.

2. how big is your data, i.e., how many cells contain a data and formula? how many applications are open while you are doing your excel worksheet? sometimes because of a low memory of your computer, the system can not proceed with the task anymore...try restarting your computer to clear the memory and do your worksheet....

hope this would help you on your challenges......

to davidgiant,

  1. YES the "if formula" is producing results just fine.
  2. The datafield isn't that large at all; it's totally-contained in the A1:AQ50 range. And during the hassle I only had Excel and a couple of other apps operating on a machine w/ 512MB of RAM. I should've had at least 1/2 of that memory still available.

  Check this out: After changing the Calculation setting to Manual [see above posts], and getting the average function to work, I went back and re-set the Calc setting [in Tools>Options>Calculation] to the original setting/Automatic, and now the app is returning the correct values just fine.

  So even though things are working O.K. now, I have no idea why things weren't working or what exactly changed to make them work now.
  If you've got any other ideas, I'd be glad to read them!

riprap.

Hi riprap1951,
bit strange that it is now set to manual. are you also running/ make use of macro's? this could put the calculation to manual.
(just wake up and will be back)
Jeroen

If your using a workbook which conotains the line: Application.Calculation = xlCalculationManual
and not: Application.Calculation = xlCalculationAutomatic
this would explain why it is set to manual and not to automatically agian.

it can also mean that these line are placed incorrectly in the macro. which might result in some case that it will be set back and some case it stays on manual.

riprap1951,

Strange is the world indeed (and Excel provides an excellent demonstration).

But can you please try to identify whether the problem is with the range being averaged,
or with the cell in which the formula is entered?

To do this, try the following two independently:

1. enter the average formula in some other cell. Ideally on a cell in a different worksheet (tab).
   does it work now?

2. in the target cell where the fascinating thing happens, enter different formulas:
   *  =average(3,4)
   *  =average([some other row])
   does it work now?

I know that this is not a solution, but it may help everyone rule out half of the wilderness.

- stochastic

To roos01 et al,

   Bottom line: I now know how to make the 'Average' function work on that worksheet, and since roos01 was the 1st to point me in the direction of looking at Tools>Options>Calculation, Calc setting Automatic | Manual, I'm accepting his answer...Thru checking and re-checking it's apparent that, every time I've got the setting in question on Automatic, the operation doesn't work as it should; I've got to enter the calculation, then place cursor in formula box, press F9, then Enter, and then the correct answer comes up.
  But when the worksheet is set to Manual, the Average function works every time the way it's supposed to. [I don't know WHY this is happening, but after testing it 15+ times it's obvious that it's so...]
 
  Thanks for all the input [:-)  Have yourselves a Fine Day.

riprap.