New spin on old problems

  • Thread starter Thread starter Arlen
  • Start date Start date
A

Arlen

I couldn't really find any perfect fit to this question as
far back as two months ago, so I'm re-pooping the question.

I am trying to keep a month-long running average of a
dentist's daily production. Obviously, the number should
not factor in the days we do not work. The $ total is
easy enough, but how do I tell Excel to scan a 31-day
range, and only divide by the number of days with figures
in them?

Thanks so much for all your help, people.

Arlen
 
Hi Arlen

In a worksheet, use the function COUNTA which counts the
non-empty cells only. It is also accessible from VBA through:

Application.WorksheetFunctions.CountA(...)


Cheers,
Martin
 
Arlen,


=SUM(A2:AE2)/COUNT(A2:AE2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Or with VBA:
y = 0
z = 0
For x = 1 To 31
y = y + Worksheets(1).Cells(x, 1).Value
If Worksheets(1).Cells(x, 1).Value = "" Or Worksheets(1).Cells(x
1).Value = 0 Then
z = z + 1
End If
Next x
avg = y /
 
Thank you all very much. The count thing works, but I'm
still hitting a snag. I'm trying to total 2 figures
together, say A1 and A2 to get an answer in A3. A3 is the
daily total production, and the entire row 3 is filled
with Row 1 + 2 formulae. The Sum and Count functions work
perfect on manually input data in rows 1 and 2, but the
Count is screwy on row 3. Here, it's just counting all 31
spaces. Is this a bug, or do I have to do fancy
maneuvering where other existing formulas are involved?

Arlen
 
Okay, the Count function is recognizing the totaling
formulas as an entry. It returns a value of 31, even when
no data is visible. How do I make it only recognize and
Count visible data?
 
In what format is the information? Are you totalling the information i
rows 1 and 2 all the way accross? How is it organized? - Piku
 
Yes, for 31 columns, A to AE, rows 1 and 2 are added
together and displayed in their respective columns in row
3. I want to use the Sum / Count function for row 3,
range A to AE in order to keep a running average of daily
totals.
 
count only counts cells displaying numbers. If your formula in the cells is
returning zero and you have the display of zeros suppressed, then you may
see the situation you describe.

in row 3 make the formula (shown for A3)

=if(And(A1="",A2=""),"",sum(A1,A2))

then drag fill this across row 3

Regards,
Tom Ogilvy
 
Thanks, TOM!

This trifle t'was terribly troubling, but now everything
works perfect. Thanks again.

Arlen
 
Back
Top