Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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]? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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]? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Rolling Average | Excel Worksheet Functions | |||
Average Function and dynamic cell address | Excel Worksheet Functions | |||
Dynamic annual average | Charts and Charting in Excel | |||
Dynamic Average Question | Excel Discussion (Misc queries) | |||
Dynamic Average | Excel Worksheet Functions |