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" schrieb im Newsbeitrag
...
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

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)

"Arlen" wrote in message
...
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

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 /

--
Message posted from http://www.ExcelForum.com

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
-----Original Message-----
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)

"Arlen" wrote in message
...
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


.

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?

-----Original Message-----
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)

"Arlen" wrote in message
...
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


.

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

--
Message posted from http://www.ExcelForum.com

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.

-----Original Message-----
In what format is the information? Are you totalling
the information in
rows 1 and 2 all the way accross? How is it organized? -
Pikus


---
Message posted from http://www.ExcelForum.com/

.

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


Arlen wrote in message
...
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?

-----Original Message-----
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)

"Arlen" wrote in message
...
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


.

Thanks, TOM!

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

Arlen


-----Original Message-----
Message unavailable