Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old March 9th 07, 07:50 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default #DIV/0!

Sorry,

I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.
If any of the cells contain a value, then COUNT will be at least 1 and
the error won't occur.

--
Regards

Roger Govier


"joeu2004" wrote in message
ups.com...
On Mar 8, 4:21 pm, "Roger Govier"
wrote:
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+
SF2!F24+SF3!F24+TA*OS!F24)
then if they sum to 0, you will get the #DIV0 error message


Are you sure? Works fine for me when all cells are on the same sheet.



  #12   Report Post  
Old March 9th 07, 10:03 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default #DIV/0!

On Mar 8, 11:50 pm, "Roger Govier"
wrote:
"joeu2004" wrote:
On Mar 8, 4:21 pm, "Roger Govier" wrote:
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+
SF2!F24+SF3!F24+TA*OS!F24)
then if they sum to 0, you will get the #DIV0 error message


Are you sure? Works fine for me when all cells are on the same sheet.


Sorry,

I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.


Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells
are blank. I suspect you are making the same mistake that I made and
thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if
all 3 cells are blank.

  #13   Report Post  
Old March 9th 07, 11:57 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default #DIV/0!

Ah, yes I can see that AVERAGE(A1+A2+A3) would work, as the + between
each item is coercing a zero value for the blank cells.
I always use
=AVERAGE(A1:A100) as I wouldn't want to type all the plus's and with
some formulae would hit the number of characters limit.

My way would be
=IF(COUNT(A1:A100)0,AVERAGE(A1:A100),"")

--
Regards

Roger Govier


"joeu2004" wrote in message
oups.com...
On Mar 8, 11:50 pm, "Roger Govier"
wrote:
"joeu2004" wrote:
On Mar 8, 4:21 pm, "Roger Govier"
wrote:
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+
SF2!F24+SF3!F24+TA*OS!F24)
then if they sum to 0, you will get the #DIV0 error message


Are you sure? Works fine for me when all cells are on the same
sheet.


Sorry,

I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.


Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells
are blank. I suspect you are making the same mistake that I made and
thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if
all 3 cells are blank.


  #14   Report Post  
Old March 9th 07, 03:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 6
Default #DIV/0!

Wow what a response, thanks. Let me take the first reference in the formula
and explain, ESP1!F24 is a cell that also has a formula in its own cell that
asks for an average of 4 other cells C24, D24, and E24 on the same
worksheet, one of those cells has data inserted and the others dont but I
still get the #DIV/0! and I wanted this formula to reflect the existing data
from C even though the other cells are still without data, thats the reason
for using the average formula. I had put the /9 because it wasnt giving me
an average but a sum total even though it said average.

--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
"joeu2004" wrote in message
ups.com...
Errata....

On Mar 8, 6:12 pm, "joeu2004" wrote:
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!F24,SF2!F24,SF3!
F24,TAO*S!F24)
[....]
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text).


Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().


  #15   Report Post  
Old March 9th 07, 05:37 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 324
Default #DIV/0!

Substitute your cell references and you will get an average of those that
contain data. Be sure to press Ctrl/Shift/Enter since this is an array
formula.

=AVERAGE(IF(J9:J20<0, L9:L20,""))
--
Best wishes,

Jim


"Jim S" wrote:

Wow what a response, thanks. Let me take the first reference in the formula
and explain, ESP1!F24 is a cell that also has a formula in its own cell that
asks for an average of 4 other cells C24, D24, and E24 on the same
worksheet, one of those cells has data inserted and the others dont but I
still get the #DIV/0! and I wanted this formula to reflect the existing data
from C even though the other cells are still without data, thats the reason
for using the average formula. I had put the /9 because it wasnt giving me
an average but a sum total even though it said average.

--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
"joeu2004" wrote in message
ups.com...
Errata....

On Mar 8, 6:12 pm, "joeu2004" wrote:
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!F24,SF2!F24,SF3!
F24,TAO¬*S!F24)
[....]
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text).


Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017