#div/0!

  • Thread starter Thread starter tryn2learn
  • Start date Start date
T

tryn2learn

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,MAY08!F44,JUN08!F44,JUL08!F44,AUG08!F44,SEP08!F44,OCT08!F44,NOV08!F44,DEC08!F44)

thank you in advance,
 
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))
 
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
 
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))
 
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.
 
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.
 
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.
 
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.
 
Back
Top