Dynamic average

T

Tasha

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

Bob Phillips

=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)
 
T

Tasha

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

Tasha

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

Tasha

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. ????
 
B

Bob Phillips

=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)
 
T

Tasha

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. ????
 
B

Bob Phillips

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

Tasha

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

Tasha

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

Harlan Grove

Tasha said:
Bob, I'm getting a #DIV/0 error. What would cause that?
Click to expand...
....

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

Harlan Grove

Harlan Grove said:
...

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

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

Tasha

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.????
 
H

Harlan Grove

Tasha said:
. . . 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. . . .
Click to expand...

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]?
 
T

Tasha

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

Harlan Grove said:
Tasha said:
. . . 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. . . .
Click to expand...

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]?
Click to expand...
 
Top