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!!!

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!!!

=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!!!

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!!!

=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!!!

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!!!

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!!!

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!!!