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

B

#### bdamin

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

G

#### Gaurav

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

S

#### Sandy Mann

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

B

#### bdamin

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.