Dynamic average
 

I will get this sheet done if it kills me!!! My sheet is set up as follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each row,
for the current date. There are 0's filled in the fields that are greater
than today. I think I've given enough information, if not....please ask!!!

Dynamic average
 

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each row,
for the current date. There are 0's filled in the fields that are greater
than today. I think I've given enough information, if not....please ask!!!

Dynamic average
 

I used this formula: =SUM($D8:$AH8)/COUNTIF(D8:AH8,"<0")
in row 2, I have 1 in cell D8, and 0's all the way across to AH8. So is
showing
an average of 1 per day. That can't be right, there is only 1 out of 26 days.

"Tasha" wrote:

I will get this sheet done if it kills me!!! My sheet is set up as follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each row,
for the current date. There are 0's filled in the fields that are greater
than today. I think I've given enough information, if not....please ask!!!

Dynamic average
 

ok. I figured out what is wrong, but don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????




"Tasha" wrote:

I will get this sheet done if it kills me!!! My sheet is set up as follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each row,
for the current date. There are 0's filled in the fields that are greater
than today. I think I've given enough information, if not....please ask!!!

Dynamic average
 

your formula did the same thing mine did. I figured out what is wrong, but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each row,
for the current date. There are 0's filled in the fields that are greater
than today. I think I've given enough information, if not....please ask!!!

Dynamic average
 

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

ok. I figured out what is wrong, but don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????



"Bob Phillips" wrote:

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

That's exactly what it does! The thing I missed was <=today, not<today

=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
ok. I figured out what is wrong, but don't know how to fix it. I don't
want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????



"Bob Phillips" wrote:

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's,
even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for
each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

ok, thanks Bob. I'm getting ready to plug it in and try it....will let you
know what happens....

"Bob Phillips" wrote:

That's exactly what it does! The thing I missed was <=today, not<today

=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
ok. I figured out what is wrong, but don't know how to fix it. I don't
want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????



"Bob Phillips" wrote:

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's,
even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for
each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

Bob, I'm getting a #DIV/0 error. What would cause that?

"Bob Phillips" wrote:

That's exactly what it does! The thing I missed was <=today, not<today

=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
ok. I figured out what is wrong, but don't know how to fix it. I don't
want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????



"Bob Phillips" wrote:

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's,
even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for
each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

ok...I went into error evaluate. It shows in the formula TODAY())
underlined, and says "A function in this formula causes the result to change
each time the spreadsheet is calculated. The final evaluation step will
match but interim steps may not." I clicked on Evaluate, and then
<=DAY(39290) is underlined, and when I click on Evaluate again, it shows
FALSE all the way down, and then <=27 is underlined and FALSE all the way
down.

"Tasha" wrote:

Bob, I'm getting a #DIV/0 error. What would cause that?

"Bob Phillips" wrote:

That's exactly what it does! The thing I missed was <=today, not<today

=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tasha" wrote in message
...
ok. I figured out what is wrong, but don't know how to fix it. I don't
want
it to count 0's that are not <= today, but it's not counting any 0's, even
those that are actually 0. ????



"Bob Phillips" wrote:

=AVERAGE(IF(D$2:AH$2<DAY(TODAY()),D3:AH3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
your formula did the same thing mine did. I figured out what is wrong,
but
don't know how to fix it. I don't want
it to count 0's that are not <= today, but it's not counting any 0's,
even
those that actually have a 0 count through today. ????


"Bob Phillips" wrote:

=AVERAGE(IF(D3:AH3<0,D3:AH3))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tasha" wrote in message
...
I will get this sheet done if it kills me!!! My sheet is set up as
follows:

Col A: doctor names
Columns D-AH: daily numbers
Row 2:dates 1-31

In cell AK2, I am trying to find the average of columns D-AH for
each
row,
for the current date. There are 0's filled in the fields that are
greater
than today. I think I've given enough information, if not....please
ask!!!

Dynamic average
 

Tasha wrote...
Bob, I'm getting a #DIV/0 error. What would cause that?

"Bob Phillips" wrote:
That's exactly what it does! The thing I missed was <=today,
not<today

=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))
....

The only way the formula shown would return #DIV/0! would be if none
of the cells in D2:AH2 were <= DAY(TODAY()). That could happen if
D2:AH2 contained either text that just looks like numbers or date
values formatted as "d". What does the formula

=COUNTIF(D2:AH2,"?*")

return? If it returns 31, then you have text in D2:AH2. If so, either
change the text to numbers by copying a blank cell, selecting D2:AH2,
and pasting special and Adding or use the formula

=AVERAGE(IF(--D$2:AH$2<=DAY(TODAY()),D3:AH3))

OTOH, if the COUNTIF formula above returns 0, what does the formula

=COUNTIF(D2:AH2,"31")

return? If it returns 31, then it would appear you have date values in
D2:AH2. If so, use the formula

=AVERAGE(IF(DAY(D$2:AH$2)<=DAY(TODAY()),D3:AH3))

Dynamic average
 

"Harlan Grove" wrote...
....
=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))
...

The only way the formula shown would return #DIV/0! would be if none
of the cells in D2:AH2 were <= DAY(TODAY()). . . .
....

OK, not strictly correct: D3:AH3 could contain cells evaluating to #DIV/0!,
but I'd suppose that would have been spotted.

Dynamic average
 

Harlan, thanks so much for replying. Okay, I checked both formulas, the last
one came up with 0, so I used the other formula and got #VALUE! error. The
cells in D2:AH2 are dates, they are formated as "d" to only show the number
of the day. The cells in D3:AH3 are as follows:
=SUMPRODUCT(('Physician Stats by Month - 2007.xls'!PHYNO=$B3)*('Physician
Stats by Month - 2007.xls'!ADMDAY=D$2)). They are pulling the number of
admits from the daily worksheet in the same workbook. I have a macro set up
to import the data into the dlywrksht, have the named ranges, PHYNO and
ADMDAY, then counts admits by physician number. It places a 0 in the field
if there are none, but also places a 0 in the field if it hasn't reached that
day yet....which seems to be what is causing my problem.????



"Harlan Grove" wrote:

"Harlan Grove" wrote...
....
=AVERAGE(IF(D$2:AH$2<=DAY(TODAY()),D3:AH3))
...

The only way the formula shown would return #DIV/0! would be if none
of the cells in D2:AH2 were <= DAY(TODAY()). . . .
....

OK, not strictly correct: D3:AH3 could contain cells evaluating to #DIV/0!,
but I'd suppose that would have been spotted.

Dynamic average
 

Tasha wrote...
. . . the last one came up with 0, so I used the other formula
and got #VALUE! error. The cells in D2:AH2 are dates, they are
formated as "d" to only show the number of the day. . . .

If you're using

=AVERAGE(IF(DAY(D$2:AH$2)<=DAY(TODAY()),D3:AH3))

are you entering it as an array formula, after typing it holding down
[Ctrl] and [Shift] keys before pressing [Enter]?

Dynamic average
 

Thank you thank you Harlan....That worked. I had forgotten to do that, and
it is perfect!!! Can't thank you enough!!!

"Harlan Grove" wrote:

Tasha wrote...
. . . the last one came up with 0, so I used the other formula
and got #VALUE! error. The cells in D2:AH2 are dates, they are
formated as "d" to only show the number of the day. . . .

If you're using

=AVERAGE(IF(DAY(D$2:AH$2)<=DAY(TODAY()),D3:AH3))

are you entering it as an array formula, after typing it holding down
[Ctrl] and [Shift] keys before pressing [Enter]?