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

  • Thread starter Thread starter bdamin
  • Start date Start date
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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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.
 
Back
Top