Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
I need to averge based on several criteria.
In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Try one of these:
This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
I'll give it a go.
Thanks for the help, I really appreciate it. Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
I've tried the formulaes as you suggested but get the result #N/A.
I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Did you array enter it as shown?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Yes
=AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
See this screencap:
http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Thanks again, I've got it working. I'd entered a bigger range of cells than
actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
I'd entered a bigger range of cells than actually have
numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Thanks. I've got it working.
On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Just use simple links:
=Sheet1!A1 -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. I've got it working. On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
Thanks.
In sheet 1, cell V3 I have the formula =COUNTIF(I2:I10000,"o2") (result = 18) I want this result to end up in cell b3 of sheet 5. So, if cell b3 on sheet 5 I've entered =sheet1!V3 ( but excel makes me select a source file, even though it's the same workbook) so it ends up as =[sheet1]Sheet1!V3. This returns the result 0 (not 18). What am I doing wrong? With regard to array formulas I've a new question. I want to count the incidences when "o2" appears in column J at the same time as "injection" appears in column e, f or g. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} but this doesn't seem to work. Can you help? Thanks. Paul "T. Valko" wrote: Just use simple links: =Sheet1!A1 -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. I've got it working. On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
In sheet 1,
excel makes me select a source file it ends up as =[sheet1]Sheet1!V3 Is your sheet name Sheet1 ? If a formula contains a reference to a sheet that doesn't exist then Excel pops that message and asks you to update values. Using links formulas should work just make sure you use the actual sheet name. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} Try this normally entered: =SUMPRODUCT(--((E1:E10000="injection")+(F1:F10000="injection")+( G1:G10000="injection")0),--(J1:J10000="O2")) Better to use cells to hold the criteria: A1 = injection B1 = O2 =SUMPRODUCT(--((E1:E10000=A1)+(F1:F10000=A1)+(G1:G10000=A1)0),--(J1:J10000=B1)) -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. In sheet 1, cell V3 I have the formula =COUNTIF(I2:I10000,"o2") (result = 18) I want this result to end up in cell b3 of sheet 5. So, if cell b3 on sheet 5 I've entered =sheet1!V3 ( but excel makes me select a source file, even though it's the same workbook) so it ends up as =[sheet1]Sheet1!V3. This returns the result 0 (not 18). What am I doing wrong? With regard to array formulas I've a new question. I want to count the incidences when "o2" appears in column J at the same time as "injection" appears in column e, f or g. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} but this doesn't seem to work. Can you help? Thanks. Paul "T. Valko" wrote: Just use simple links: =Sheet1!A1 -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. I've got it working. On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
You're a star. Thanks so much.
I had tried the name of the sheet in the link formula but I think the problem lay in that the name a space in it. i.e. sheet name changed from Diagnostic OGD to DiagnosticOGD and it worked. Thanks, Paul "T. Valko" wrote: In sheet 1, excel makes me select a source file it ends up as =[sheet1]Sheet1!V3 Is your sheet name Sheet1 ? If a formula contains a reference to a sheet that doesn't exist then Excel pops that message and asks you to update values. Using links formulas should work just make sure you use the actual sheet name. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} Try this normally entered: =SUMPRODUCT(--((E1:E10000="injection")+(F1:F10000="injection")+( G1:G10000="injection")0),--(J1:J10000="O2")) Better to use cells to hold the criteria: A1 = injection B1 = O2 =SUMPRODUCT(--((E1:E10000=A1)+(F1:F10000=A1)+(G1:G10000=A1)0),--(J1:J10000=B1)) -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. In sheet 1, cell V3 I have the formula =COUNTIF(I2:I10000,"o2") (result = 18) I want this result to end up in cell b3 of sheet 5. So, if cell b3 on sheet 5 I've entered =sheet1!V3 ( but excel makes me select a source file, even though it's the same workbook) so it ends up as =[sheet1]Sheet1!V3. This returns the result 0 (not 18). What am I doing wrong? With regard to array formulas I've a new question. I want to count the incidences when "o2" appears in column J at the same time as "injection" appears in column e, f or g. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} but this doesn't seem to work. Can you help? Thanks. Paul "T. Valko" wrote: Just use simple links: =Sheet1!A1 -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. I've got it working. On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging based on several criteria
I think the problem lay in that the name a space in it.
When a sheet name contains spaces or is a number then you need to quote the sheet name like this: ='Diagnostic OGD'!A1 ='10'!A1 Another way to do it is to select the cell you want to copy/link: Goto EditCopy Then select the cell where you want the value to appear Then, EditPaste Specialclick the Paste Link button This way Excel takes care of the sheet name for you. Yet another way: Select the cell where you want the value to appear Type in the equal sign: = Then select the cell you want to link to Hit ENTER (or click the "checkmark" icon in the formula bar) -- Biff Microsoft Excel MVP "Paul" wrote in message ... You're a star. Thanks so much. I had tried the name of the sheet in the link formula but I think the problem lay in that the name a space in it. i.e. sheet name changed from Diagnostic OGD to DiagnosticOGD and it worked. Thanks, Paul "T. Valko" wrote: In sheet 1, excel makes me select a source file it ends up as =[sheet1]Sheet1!V3 Is your sheet name Sheet1 ? If a formula contains a reference to a sheet that doesn't exist then Excel pops that message and asks you to update values. Using links formulas should work just make sure you use the actual sheet name. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} Try this normally entered: =SUMPRODUCT(--((E1:E10000="injection")+(F1:F10000="injection")+( G1:G10000="injection")0),--(J1:J10000="O2")) Better to use cells to hold the criteria: A1 = injection B1 = O2 =SUMPRODUCT(--((E1:E10000=A1)+(F1:F10000=A1)+(G1:G10000=A1)0),--(J1:J10000=B1)) -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. In sheet 1, cell V3 I have the formula =COUNTIF(I2:I10000,"o2") (result = 18) I want this result to end up in cell b3 of sheet 5. So, if cell b3 on sheet 5 I've entered =sheet1!V3 ( but excel makes me select a source file, even though it's the same workbook) so it ends up as =[sheet1]Sheet1!V3. This returns the result 0 (not 18). What am I doing wrong? With regard to array formulas I've a new question. I want to count the incidences when "o2" appears in column J at the same time as "injection" appears in column e, f or g. I've tried {=((COUNTIF(E2:G10000,"injection")*(J2:J10000="O1" )))} but this doesn't seem to work. Can you help? Thanks. Paul "T. Valko" wrote: Just use simple links: =Sheet1!A1 -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks. I've got it working. On a seperate issue (if you don't mind) I have a workbook with 4 worksheets. Each worksheet contains a section with formulas summarising the data on that worksheet. I'd like to copy these summary tables onto a 5th sheet so they can be read easily. If possible I'd like to maintain the formulas so that if I make a change to the data on sheet 1 it is updated in the summary on sheet 5. Any help would be much appreciated. Paul "T. Valko" wrote: I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry You can do that. You just can't use entire column references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Thanks again, I've got it working. I'd entered a bigger range of cells than actually have numbers in order to anticipate future data entry and this seemed to be the problem. I've changed to the appropriate number of cells and it seems to have worked. Thanks so much for your help, Paul "T. Valko" wrote: See this screencap: http://img154.imageshack.us/img154/6089/avgifwe2.jpg As you'll see, the formula returns the correct result. get the result #N/A. Do you have any #N/A errors in any of your ranges? If so, that's why you're getting that result. -- Biff Microsoft Excel MVP "Paul" wrote in message ... Yes =AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N1000 0)) I have copied this from the cell. y2 is a cell containing the number 70 y3 is a cell containing the word midazolm The N column contains the numbers I want to average. Thanks "Bob Phillips" wrote: Did you array enter it as shown? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul" wrote in message ... I've tried the formulaes as you suggested but get the result #N/A. I can't get the averageifs to work at all (but I have office 2003). Any suggestions? Thanks, Paul "T. Valko" wrote: Try one of these: This array formula** works in all versions of Excel : =AVERAGE(IF((C1:C15<70)*(M1:M15="X"),N1:N15)) Or, this normally entered formula in Excel 2007 only: =AVERAGEIFS(N1:N15,C1:C15,"<70",M1:M15,"X") Better to use cells to hold the criteria: A1 = 70 B1 = X =AVERAGE(IF((C1:C15<A1)*(M1:M15=B1),N1:N15)) =AVERAGEIFS(N1:N15,C1:C15,"<"&A1,M1:M15,B1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paul" wrote in message ... I need to averge based on several criteria. In column C I have ages In column M I have words for medication used by a patient In column N I have numbers I'd like to search column C for certain ages and then M for a drug and then average the dose (in column N) for those meeting criteria for both colums C and M i.e. average dose (column N) for people under 70 (column C) on drug x (column M). I'd really appreciate any help. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging set of data based on the time | Excel Worksheet Functions | |||
HELP: Averaging values if they meet certain criteria | Excel Worksheet Functions | |||
Averaging Columns based on a Text String | New Users to Excel | |||
When Averaging a column, exclude value based on another cell value | Excel Worksheet Functions | |||
Averaging data that meets a criteria | Excel Worksheet Functions |