ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   #div/0! (https://www.excelbanter.com/excel-discussion-misc-queries/178043-div-0-a.html)

tryn2learn

#div/0!
 
I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,

JE McGimpsey

#div/0!
 
AVERAGE will ignore blanks as long as at least one cell has a numeric
value, so check your Jan08!F44 and Feb08!F44 cells...

Are you sure you don't have a #DIV/0 in one of the others?

If your sheets are in date order, you might try:

=IF(COUNT(Jan08:Dec08!F44)=0,"No data",AVERAGE(Jan08:Dec08!F44))



In article ,
tryn2learn wrote:

I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44
,AUG08!F44,SEP08!F44,OCT08!F44,NOV08!F44,DEC08!F44 )

thank you in advance,


FSt1

#div/0!
 
hi
you could roll it up in an if fomula as this example fomula illistrates...
=IF(SUM(D2:D6)=0,0,AVERAGE(D2,D3,D4,D5,D6))

regards
FSt1

"tryn2learn" wrote:

I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,


David Biddulph[_2_]

#div/0!
 
You shouldn't get a divide by zero unless *all* the cells in the range are
empty (or contain text instead of numbers). If you've got data for Jan &
Feb, it shouldn't matter that you've got nothing for Mar onwards.

You could protect against all being blank by something like:
=IF(COUNT(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44, MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44, OCT08!F44,NOV08!F44,DEC08!F44)=0,"no
input
data",AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08! F44,MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08! F44,OCT08!F44,NOV08!F44,DEC08!F44))
--
David Biddulph

"tryn2learn" wrote in message
...
I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,




tryn2learn

#div/0!
 
I am getting the #DIV/0 in the other cells (March-December)

I applied the formula and it does give me the no data, but my problem is i
need it to give me the Jan-Feb average on a year to date basis.

I appreciate your help.

"JE McGimpsey" wrote:

AVERAGE will ignore blanks as long as at least one cell has a numeric
value, so check your Jan08!F44 and Feb08!F44 cells...

Are you sure you don't have a #DIV/0 in one of the others?

If your sheets are in date order, you might try:

=IF(COUNT(Jan08:Dec08!F44)=0,"No data",AVERAGE(Jan08:Dec08!F44))



In article ,
tryn2learn wrote:

I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44
,AUG08!F44,SEP08!F44,OCT08!F44,NOV08!F44,DEC08!F44 )

thank you in advance,



tryn2learn

#div/0!
 
I'm wondering since i'm trying to get an average of time across a year should
i take a different approach.

i have a cell in each month dedicated to this time (F44) as stated in my non
working formula :)

my problem is until i actually have data for mar-dec the dedicated cell will
remain as #DIV\0!

i thought about the zerno/0 option but then that puts 12:00am in the
field... using that will through off my average becuase 10 of the 12 months
will show as 12:00am for an average time.

again i appreciate your help.

"David Biddulph" wrote:

You shouldn't get a divide by zero unless *all* the cells in the range are
empty (or contain text instead of numbers). If you've got data for Jan &
Feb, it shouldn't matter that you've got nothing for Mar onwards.

You could protect against all being blank by something like:
=IF(COUNT(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44, MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44, OCT08!F44,NOV08!F44,DEC08!F44)=0,"no
input
data",AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08! F44,MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08! F44,OCT08!F44,NOV08!F44,DEC08!F44))
--
David Biddulph

"tryn2learn" wrote in message
...
I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,





David Biddulph[_2_]

#div/0!
 
Don't thank me for my help, as I don't think you've read what I said.

Are you saying that the problem with your January to December average
formula isn't that the March - September cells are empty, but that they in
turn already contain #DIV/0! because they are trying to average within each
month across a range of entirely blank cells?
If so, tackle it in the same way that I suggested for the average across the
year, and test for the count not being zero.
--
David Biddulph

"tryn2learn" wrote in message
...
I'm wondering since i'm trying to get an average of time across a year
should
i take a different approach.

i have a cell in each month dedicated to this time (F44) as stated in my
non
working formula :)

my problem is until i actually have data for mar-dec the dedicated cell
will
remain as #DIV\0!

i thought about the zerno/0 option but then that puts 12:00am in the
field... using that will through off my average becuase 10 of the 12
months
will show as 12:00am for an average time.

again i appreciate your help.

"David Biddulph" wrote:

You shouldn't get a divide by zero unless *all* the cells in the range
are
empty (or contain text instead of numbers). If you've got data for Jan &
Feb, it shouldn't matter that you've got nothing for Mar onwards.

You could protect against all being blank by something like:
=IF(COUNT(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44, MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44, OCT08!F44,NOV08!F44,DEC08!F44)=0,"no
input
data",AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08! F44,MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08! F44,OCT08!F44,NOV08!F44,DEC08!F44))
--
David Biddulph

"tryn2learn" wrote in message
...
I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,







tryn2learn

#div/0!
 
I wanted to thank you because i believe it all helps.

....but yes you are exactly right... my problem is that month to month beyond
feb i already have the #DIV\0! error.

i will take the approach you suggest on a month to month resolve which
should then inturn fix my YTD totals.

"David Biddulph" wrote:

Don't thank me for my help, as I don't think you've read what I said.

Are you saying that the problem with your January to December average
formula isn't that the March - September cells are empty, but that they in
turn already contain #DIV/0! because they are trying to average within each
month across a range of entirely blank cells?
If so, tackle it in the same way that I suggested for the average across the
year, and test for the count not being zero.
--
David Biddulph

"tryn2learn" wrote in message
...
I'm wondering since i'm trying to get an average of time across a year
should
i take a different approach.

i have a cell in each month dedicated to this time (F44) as stated in my
non
working formula :)

my problem is until i actually have data for mar-dec the dedicated cell
will
remain as #DIV\0!

i thought about the zerno/0 option but then that puts 12:00am in the
field... using that will through off my average becuase 10 of the 12
months
will show as 12:00am for an average time.

again i appreciate your help.

"David Biddulph" wrote:

You shouldn't get a divide by zero unless *all* the cells in the range
are
empty (or contain text instead of numbers). If you've got data for Jan &
Feb, it shouldn't matter that you've got nothing for Mar onwards.

You could protect against all being blank by something like:
=IF(COUNT(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44, MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44, OCT08!F44,NOV08!F44,DEC08!F44)=0,"no
input
data",AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08! F44,MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08! F44,OCT08!F44,NOV08!F44,DEC08!F44))
--
David Biddulph

"tryn2learn" wrote in message
...
I am trying to get a total for a year to date.
in doing my year to date sheet i of course don't have data yet for
march-december
and because of that my total colum shows as #div/0!

the formula i am using reads as:
=AVERAGE(JAN08!F44,FEB08!F44,MAR08!F44,APR08!F44,M AY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,O CT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,








All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com