counta & countblank for "month-to-date/year-to-date"?

[magsgundah]

I have a document that I basically use only to track whether specific weekly
or monthly reports have been submitted. I need to know the percentage of
each report submitted for both the current month and for the year.

I've been having it return percentages by using:
counta(range)/(counta(range) + countblank(range))

I fill down the column of specific reports and get accurate percentages per
report.

Is there a simpler way to do this? Each week I update the range in the
first cell to include the current week and fill down. That works for me, but
my supervisor believes there has to be a more elegant solution. It seems to
me that there should be as well, and If there is, I'd rather use it. If
there isn't, the solution we have works well enough, I guess.

 

[ShaneDevenshire]

Hi,

Show us what your data looks like. Second, tell us whether you are running
the percentage by week, month or year - In you discussion you say every week?
Tell us how you know if a particular report is for a specific week, month or
year - is there a date column? Why are there blanks, do you have a list of
reports that should be turned in and then you indicate somehow that it hasn't
been?

 

[magsgundah]

An example - assume Wk 5 is the current week -

Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 ... Wk52. MTD
YtD


Location 1 a a a a
75% 80%
Location 2 a a a a a
100% 100%
Location 3 a a a a
100% 80%
100% 100% 67% 100% 67%


(The "a" displays as a checkmark in the font that these reports were set up
with by the person who used to do this.)

The reports are sent weekly by the locations of our company. I record them
by typing an "a" into the cell for that location in the appropriate week. If
a report is not submitted the cell is left blank. It would not be uncommon
for Location 1 to submit Wk 3 and Wk 6 in Wk 6, or for location 3 to submit
Wk 5 and Wk6 during week 6, if that affects the solution.

So, while the reports are submitted weekly, I track mtd and ytd progress.

Is there a better way to track these rows than
counta(range)/(counta(range)+countblank(range)) ? It works but it seems, for
lack of a better word, clumsy.


Thank you very much!

 

[ShaneDevenshire]

Hi,

1. First, I suspect that you have no trouble with the column calculations
because the number of locations probably remain constant. So the formulas
would read something like =COUNTA(B2:B5)/4 and this could be copied all the
way across the row for 52 weeks. To save having to manually copy these
formulas every week you can choose the command Tools, Options, View tab, and
uncheck Zero values. If this is not something that you can do on this sheet,
then you can modify the formula to read
=IF(COLUMN()>$A$1+1,"",COUNTA(B2:B4)/3) This formula can be filled across
for all 52 weeks and it would only display calculations for weeks up to the
current week. The key for that is that A1 contains the number of the current
week, as in your example 5. With the first weeks marks in column B running
from B2:B4 (only three locations in my example.)
2. MTD calculations - I can't help you until you tell me how you relate wk
numbers to months? If 4 weeks = 1 month, each year would have 13 months?
3. YTD calculation - =COUNTA(B2:BA2)/$A$1 again A1 contains the current wk
number and the range B2:BA2 in the 52 weeks. Nothing to modify here, just
copy it down.


None of these formulas need to be modified once entered.