Obtain a Year to Date result without all cells of data being fille
 

I need to obtain a YTD result at the end of every month. I can not get
that result without temporarily deleteing the cells that do not yet have data
in them. (These cells refer back to other calculations in the workbook).
Is there anyway to have the YTD calculate with the data that is present and
ignore the unfilled data and then automatically pickup the upcoming months
when they have data?
Jan-08 54% Jan-08 54%
Feb-08 89% Feb-08 89%
Mar-08 82% Mar-08 82%
Apr-08 80% Apr-08 80%
May-08 0% May-08
Jun-08 0% Jun-08
Jul-08 0% Jul-08
Aug-08 0% Aug-08
Sep-08 0% Sep-08
Oct-08 0% Oct-08
Nov-08 0% Nov-08
Dec-08 0% Dec-08
YTD #DIV/0! YTD 73%
no result result

Obtain a Year to Date result without all cells of data being fille
 

What do you mean by result? what is the formula that you are using?


"bdamin" wrote in message
...
I need to obtain a YTD result at the end of every month. I can not get
that result without temporarily deleteing the cells that do not yet have
data
in them. (These cells refer back to other calculations in the workbook).
Is there anyway to have the YTD calculate with the data that is present
and
ignore the unfilled data and then automatically pickup the upcoming months
when they have data?
Jan-08 54% Jan-08 54%
Feb-08 89% Feb-08 89%
Mar-08 82% Mar-08 82%
Apr-08 80% Apr-08 80%
May-08 0% May-08
Jun-08 0% Jun-08
Jul-08 0% Jul-08
Aug-08 0% Aug-08
Sep-08 0% Sep-08
Oct-08 0% Oct-08
Nov-08 0% Nov-08
Dec-08 0% Dec-08
YTD #DIV/0! YTD 73%
no result result

Obtain a Year to Date result without all cells of data being fille
 

If I understand you correctly, with headers in A1:B1 and the data in A2:B13
try:

=IF(COUNT(B2:B13)=0,"",SUM(B2:B13)/(SUMPRODUCT((B2:B13<0)*(B2:B13<""))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"bdamin" wrote in message
...
I need to obtain a YTD result at the end of every month. I can not get
that result without temporarily deleteing the cells that do not yet have
data
in them. (These cells refer back to other calculations in the workbook).
Is there anyway to have the YTD calculate with the data that is present
and
ignore the unfilled data and then automatically pickup the upcoming months
when they have data?
Jan-08 54% Jan-08 54%
Feb-08 89% Feb-08 89%
Mar-08 82% Mar-08 82%
Apr-08 80% Apr-08 80%
May-08 0% May-08
Jun-08 0% Jun-08
Jul-08 0% Jul-08
Aug-08 0% Aug-08
Sep-08 0% Sep-08
Oct-08 0% Oct-08
Nov-08 0% Nov-08
Dec-08 0% Dec-08
YTD #DIV/0! YTD 73%
no result result

Obtain a Year to Date result without all cells of data being f
 

There is another column with calls offered by month. The individual service
level cells are automatically filled in from another worksheet where they are
calculated. To get the YTD service level, the formula is ((every month
service level)* (every months calls offered)/ total calls offered. I tried
to paste a print screen but it wont allow me. It is very difficult to
verbally explain.

"Gaurav" wrote:

What do you mean by result? what is the formula that you are using?


"bdamin" wrote in message
...
I need to obtain a YTD result at the end of every month. I can not get
that result without temporarily deleteing the cells that do not yet have
data
in them. (These cells refer back to other calculations in the workbook).
Is there anyway to have the YTD calculate with the data that is present
and
ignore the unfilled data and then automatically pickup the upcoming months
when they have data?
Jan-08 54% Jan-08 54%
Feb-08 89% Feb-08 89%
Mar-08 82% Mar-08 82%
Apr-08 80% Apr-08 80%
May-08 0% May-08
Jun-08 0% Jun-08
Jul-08 0% Jul-08
Aug-08 0% Aug-08
Sep-08 0% Sep-08
Oct-08 0% Oct-08
Nov-08 0% Nov-08
Dec-08 0% Dec-08
YTD #DIV/0! YTD 73%
no result result