| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
