averaging based on several criteria

[Paul]

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

 

[T. Valko]

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)

 

[Paul]

I'll give it a go.
Thanks for the help, I really appreciate it.

Paul

 

[Paul]

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

 

[Bob Phillips]

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]

Yes

=AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N10000))

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

 

[T. Valko]

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.

 

[Paul]

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]

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.

 

[Paul]

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]

Just use simple links:

=Sheet1!A1

 

[Paul]

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]

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

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

Just use simple links:

=Sheet1!A1

 

[Paul]

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

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

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

Just use simple links:

=Sheet1!A1

--
Biff
Microsoft Excel MVP


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



:

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


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




:

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


Yes

=AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N10000))

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



:

Did you array enter it as shown?

--
HTH

Bob

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

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

:

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


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

 

[T. Valko]

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 Edit>Copy
Then select the cell where you want the value to appear
Then, Edit>Paste Special>click 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

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

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

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


:

Just use simple links:

=Sheet1!A1

--
Biff
Microsoft Excel MVP


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



:

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


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




:

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


Yes

=AVERAGE(IF((C2:C10000<Y2)*(M2:M10000=Y3),N2:N10000))

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



:

Did you array enter it as shown?

--
HTH

Bob

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

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

:

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


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