New spin on old problems

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
 
M

Martin Seelhofer

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
 
B

Bob Phillips

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)
 
P

pikus

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 /
 
A

Arlen

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
 
A

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?
 
P

pikus

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
 
G

Guest

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.
 
T

Tom Ogilvy

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
 
A

Arlen

Thanks, TOM!

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

Arlen
 
Top