I got the average already, thanks to you... but is there any way that i can
put a date range on the formula? something like i can only count the number
of very satisfied ratings this september?

can this be possible?
=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31))

is this possible?

Thank you.


"T. Valko" wrote:

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100))

Ok, I'm assuming you want the count of "very satisfied" for a particular
person.

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3))

If you're using Excel 2007:

=COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3)

I'm not sure about your average. *Exactly* what do you want to average?


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Thank you so much for this.. A follow up question, im trying to get the
number and average of very satisfied comments of a lis of students..

ex.

This is the Data Worksheet

A B
mike Very Satisfied
Kris Very Satisfied
Kris Satisfied
Mike Satisfied
Mike Very Satisfied
Mike Not Satisfied
Tamy Satisfied
Tamy Satisfied

and this is my formula :

This is a cell from a different Worksheet

D3 = Very Satisfied

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100))

Thank you so much!

"T. Valko" wrote:

For the count:

Data in the range A2:A7...

C2 = satisified

=COUNTIF(A2:A7,C2)

For the percentage:

Assuming the count formula is in D2...

=D2/COUNTA(A2:A7)

Format as Percentage

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi Again,

Got a question again.. How can i count text or words?

Ex.

in column A, i got texts that is equal to "Very Satisfied" and
"Satisfied"
and others.

A
Very Satisfied
Satisfied
Not Satisfied
Very Satisfied
Very Satisfied
Satisfied

How can i count the cells that contains "Very Satisfied"? and is there
any
way for me to get the Average of "Very Satisfied" against the total
number
of
data that is in column A

thank you. :)


"T. Valko" wrote:

I see that you're using the MS web interface so in the list on the
left
side
select Excel Programming.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
ahh ok, thank you so much.. and do happen to know where can go i
that
forum?
do u know the link to that furom? thanks. =)

"T. Valko" wrote:

Hmmm....

I'm not sure. You might be able to do that with an event macro but
I
don't
know how to do it.

Try posting this question in the programming forum. If A1 contains
a
formula
make sure you note that in your question.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi, got a Question again... is there any formula that can
automatically
create a comment in a cell?

ex.

A1 = 80% and can we have a comment on that, that automatically
that
says
"Passed"

Thanks.

"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array
formulas
in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a
#VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet
and
B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im
trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article
,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering
the
results.
It
figured 79% when it should have been 94%. 94% was
returned
using
the
simple
average formula. I'm guessing I need to nest something to
not
count
nulls?
Help!!!

To include a date range..

Data!A2:A100 = names
Data!B2:B100 = response
Data!C2:C100 = dates

These are the criteria:

B2 = some name = Mike
C2 = some response = very satisfied
D2 = start date = 9/1/2009
E2 = end date = 9/30/2009

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2))

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
I got the average already, thanks to you... but is there any way that i can
put a date range on the formula? something like i can only count the
number
of very satisfied ratings this september?

can this be possible?
=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31))

is this possible?

Thank you.


"T. Valko" wrote:

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100))

Ok, I'm assuming you want the count of "very satisfied" for a particular
person.

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3))

If you're using Excel 2007:

=COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3)

I'm not sure about your average. *Exactly* what do you want to average?


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Thank you so much for this.. A follow up question, im trying to get the
number and average of very satisfied comments of a lis of students..

ex.

This is the Data Worksheet

A B
mike Very Satisfied
Kris Very Satisfied
Kris Satisfied
Mike Satisfied
Mike Very Satisfied
Mike Not Satisfied
Tamy Satisfied
Tamy Satisfied

and this is my formula :

This is a cell from a different Worksheet

D3 = Very Satisfied

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100))

Thank you so much!

"T. Valko" wrote:

For the count:

Data in the range A2:A7...

C2 = satisified

=COUNTIF(A2:A7,C2)

For the percentage:

Assuming the count formula is in D2...

=D2/COUNTA(A2:A7)

Format as Percentage

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi Again,

Got a question again.. How can i count text or words?

Ex.

in column A, i got texts that is equal to "Very Satisfied" and
"Satisfied"
and others.

A
Very Satisfied
Satisfied
Not Satisfied
Very Satisfied
Very Satisfied
Satisfied

How can i count the cells that contains "Very Satisfied"? and is
there
any
way for me to get the Average of "Very Satisfied" against the total
number
of
data that is in column A

thank you. :)


"T. Valko" wrote:

I see that you're using the MS web interface so in the list on the
left
side
select Excel Programming.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
ahh ok, thank you so much.. and do happen to know where can go i
that
forum?
do u know the link to that furom? thanks. =)

"T. Valko" wrote:

Hmmm....

I'm not sure. You might be able to do that with an event macro
but
I
don't
know how to do it.

Try posting this question in the programming forum. If A1
contains
a
formula
make sure you note that in your question.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi, got a Question again... is there any formula that can
automatically
create a comment in a cell?

ex.

A1 = 80% and can we have a comment on that, that automatically
that
says
"Passed"

Thanks.

"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array
formulas
in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key
and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote
in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a
#VALUE!
error..
$A2
is the name that is suppose to be equal in the Data!
worksheet
and
B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im
trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article
,
"Spottkitty"
wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an
average.
Problem...if
the field is blank it's counting it as zero and lowering
the
results.
It
figured 79% when it should have been 94%. 94% was
returned
using
the
simple
average formula. I'm guessing I need to nest something
to
not
count
nulls?
Help!!!

Thank you so much! everything is working now. Cheers!

"T. Valko" wrote:

To include a date range..

Data!A2:A100 = names
Data!B2:B100 = response
Data!C2:C100 = dates

These are the criteria:

B2 = some name = Mike
C2 = some response = very satisfied
D2 = start date = 9/1/2009
E2 = end date = 9/30/2009

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2))

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
I got the average already, thanks to you... but is there any way that i can
put a date range on the formula? something like i can only count the
number
of very satisfied ratings this september?

can this be possible?
=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31))

is this possible?

Thank you.


"T. Valko" wrote:

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100))

Ok, I'm assuming you want the count of "very satisfied" for a particular
person.

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3))

If you're using Excel 2007:

=COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3)

I'm not sure about your average. *Exactly* what do you want to average?


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Thank you so much for this.. A follow up question, im trying to get the
number and average of very satisfied comments of a lis of students..

ex.

This is the Data Worksheet

A B
mike Very Satisfied
Kris Very Satisfied
Kris Satisfied
Mike Satisfied
Mike Very Satisfied
Mike Not Satisfied
Tamy Satisfied
Tamy Satisfied

and this is my formula :

This is a cell from a different Worksheet

D3 = Very Satisfied

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100))

Thank you so much!

"T. Valko" wrote:

For the count:

Data in the range A2:A7...

C2 = satisified

=COUNTIF(A2:A7,C2)

For the percentage:

Assuming the count formula is in D2...

=D2/COUNTA(A2:A7)

Format as Percentage

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi Again,

Got a question again.. How can i count text or words?

Ex.

in column A, i got texts that is equal to "Very Satisfied" and
"Satisfied"
and others.

A
Very Satisfied
Satisfied
Not Satisfied
Very Satisfied
Very Satisfied
Satisfied

How can i count the cells that contains "Very Satisfied"? and is
there
any
way for me to get the Average of "Very Satisfied" against the total
number
of
data that is in column A

thank you. :)


"T. Valko" wrote:

I see that you're using the MS web interface so in the list on the
left
side
select Excel Programming.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
ahh ok, thank you so much.. and do happen to know where can go i
that
forum?
do u know the link to that furom? thanks. =)

"T. Valko" wrote:

Hmmm....

I'm not sure. You might be able to do that with an event macro
but
I
don't
know how to do it.

Try posting this question in the programming forum. If A1
contains
a
formula
make sure you note that in your question.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi, got a Question again... is there any formula that can
automatically
create a comment in a cell?

ex.

A1 = 80% and can we have a comment on that, that automatically
that
says
"Passed"

Thanks.

"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array
formulas
in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key
and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote
in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a
#VALUE!
error..
$A2
is the name that is suppose to be equal in the Data!
worksheet
and
B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im
trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article
,
"Spottkitty"
wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an
average.
Problem...if
the field is blank it's counting it as zero and lowering
the
results.
It
figured 79% when it should have been 94%. 94% was
returned
using
the
simple
average formula. I'm guessing I need to nest something
to
not
count
nulls?
Help!!!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Thank you so much! everything is working now. Cheers!

"T. Valko" wrote:

To include a date range..

Data!A2:A100 = names
Data!B2:B100 = response
Data!C2:C100 = dates

These are the criteria:

B2 = some name = Mike
C2 = some response = very satisfied
D2 = start date = 9/1/2009
E2 = end date = 9/30/2009

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C2),--(Data!C2:C100=D2),--(Data!C2:C100<=E2))

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
I got the average already, thanks to you... but is there any way that i
can
put a date range on the formula? something like i can only count the
number
of very satisfied ratings this september?

can this be possible?
=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3),--(A1:A31=[Date.xls]Date!A1:A31))

is this possible?

Thank you.


"T. Valko" wrote:

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Dat a!B2:B100))

Ok, I'm assuming you want the count of "very satisfied" for a
particular
person.

=SUMPRODUCT(--(Data!A2:A100=B2),--(Data!B2:B100=C3))

If you're using Excel 2007:

=COUNTIFS(Data!A2:A100,B2,Data!B2:B100,C3)

I'm not sure about your average. *Exactly* what do you want to
average?


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Thank you so much for this.. A follow up question, im trying to get
the
number and average of very satisfied comments of a lis of
students..

ex.

This is the Data Worksheet

A B
mike Very Satisfied
Kris Very Satisfied
Kris Satisfied
Mike Satisfied
Mike Very Satisfied
Mike Not Satisfied
Tamy Satisfied
Tamy Satisfied

and this is my formula :

This is a cell from a different Worksheet

D3 = Very Satisfied

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data !B2:B100))

Thank you so much!

"T. Valko" wrote:

For the count:

Data in the range A2:A7...

C2 = satisified

=COUNTIF(A2:A7,C2)

For the percentage:

Assuming the count formula is in D2...

=D2/COUNTA(A2:A7)

Format as Percentage

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi Again,

Got a question again.. How can i count text or words?

Ex.

in column A, i got texts that is equal to "Very Satisfied" and
"Satisfied"
and others.

A
Very Satisfied
Satisfied
Not Satisfied
Very Satisfied
Very Satisfied
Satisfied

How can i count the cells that contains "Very Satisfied"? and is
there
any
way for me to get the Average of "Very Satisfied" against the
total
number
of
data that is in column A

thank you. :)


"T. Valko" wrote:

I see that you're using the MS web interface so in the list on
the
left
side
select Excel Programming.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
ahh ok, thank you so much.. and do happen to know where can go
i
that
forum?
do u know the link to that furom? thanks. =)

"T. Valko" wrote:

Hmmm....

I'm not sure. You might be able to do that with an event
macro
but
I
don't
know how to do it.

Try posting this question in the programming forum. If A1
contains
a
formula
make sure you note that in your question.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote
in
message
...
Hi, got a Question again... is there any formula that can
automatically
create a comment in a cell?

ex.

A1 = 80% and can we have a comment on that, that
automatically
that
says
"Passed"

Thanks.

"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array
formulas
in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key
and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi"
wrote
in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a
#VALUE!
error..
$A2
is the name that is suppose to be equal in the Data!
worksheet
and
B$1
is
the
date that is suppose to be equal in the Data! worksheet.
Im
trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or
CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article
,
"Spottkitty"
wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an
average.
Problem...if
the field is blank it's counting it as zero and
lowering
the
results.
It
figured 79% when it should have been 94%. 94% was
returned
using
the
simple
average formula. I'm guessing I need to nest
something
to
not
count
nulls?
Help!!!

Hi!

It's me again :)

I would like to ask for your help again..

How can i get the data from 1 worksheet to another?

Sheet 1

A B C
Date Name ACD
March 1 Mike 34
March 1 John 100
March 1 James 75
March 2 John 80


In Sheet 2

How can I lookup or display the data from sheet 1 to sheet to sheet 2?
ex. How can i display the ACD of John for March 1?

A B C
Date NAME ACD
March 1 John ?

What would be my formula in C3 for me to display the ACD of john for march 1?

Thank you so much for your help!

=)




"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE! error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1 is
the
date that is suppose to be equal in the Data! worksheet. Im trying to use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the results.
It
figured 79% when it should have been 94%. 94% was returned using the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!

Try this array formula

=INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2 )*(Sheet1!$B$2:$B$200=B2),0))


--

HTH

Bob

"Hijosdelongi" wrote in message
...
Hi!

It's me again :)

I would like to ask for your help again..

How can i get the data from 1 worksheet to another?

Sheet 1

A B C
Date Name ACD
March 1 Mike 34
March 1 John 100
March 1 James 75
March 2 John 80


In Sheet 2

How can I lookup or display the data from sheet 1 to sheet to sheet 2?
ex. How can i display the ACD of John for March 1?

A B C
Date NAME ACD
March 1 John ?

What would be my formula in C3 for me to display the ACD of john for march
1?

Thank you so much for your help!

=)




"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned using
the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!

Thanks a lot Bob, it worked great!

=)



"Bob Phillips" wrote:

Try this array formula

=INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2 )*(Sheet1!$B$2:$B$200=B2),0))


--

HTH

Bob

"Hijosdelongi" wrote in message
...
Hi!

It's me again :)

I would like to ask for your help again..

How can i get the data from 1 worksheet to another?

Sheet 1

A B C
Date Name ACD
March 1 Mike 34
March 1 John 100
March 1 James 75
March 2 John 80


In Sheet 2

How can I lookup or display the data from sheet 1 to sheet to sheet 2?
ex. How can i display the ACD of John for March 1?

A B C
Date NAME ACD
March 1 John ?

What would be my formula in C3 for me to display the ACD of john for march
1?

Thank you so much for your help!

=)




"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned using
the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!






.

Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE! error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1 is
the
date that is suppose to be equal in the Data! worksheet. Im trying to use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the results.
It
figured 79% when it should have been 94%. 94% was returned using the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!

Try this...

=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John")

Better to use cells to hold the criteria.

E2 = John
F2 = VS
G2 = VD

=SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2)

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned using
the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!

THank you so much! :)


"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John")

Better to use cells to hold the criteria.

E2 = John
F2 = VS
G2 = VD

=SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2)

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned using
the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!






.

Hi,

Why is that its giving me a 0 value?

The survey its actually like this :

VS = 100
S = 0
NS = 0
VD = -100
D = -100

So if John has

John VS
John VD
John D
John S
John NS
Mike D
Mike VS
Mike VS


the formula again is

(VS + ( D + VD ))/Total number of surveys


So its like this

(100 + ( -200 )) / 5

So the answer for this should be -20


Thanks again for your help :)




"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John")

Better to use cells to hold the criteria.

E2 = John
F2 = VS
G2 = VD

=SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2)

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned using
the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!






.

The survey its actually like this

So, you have a separate table with the code values?

Ok, rearange your code table so that it's sorted in ascending order like
this:

D...-100
NS...0
S...0
VD...-100
VS...100

Assume that table is in the range A1:B5

Your list of names and codes is in the range D1:E8.

Array entered** :

=AVERAGE(IF(D1:D8="John",LOOKUP(E1:E8,A1:B5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

Why is that its giving me a 0 value?

The survey its actually like this :

VS = 100
S = 0
NS = 0
VD = -100
D = -100

So if John has

John VS
John VD
John D
John S
John NS
Mike D
Mike VS
Mike VS


the formula again is

(VS + ( D + VD ))/Total number of surveys


So its like this

(100 + ( -200 )) / 5

So the answer for this should be -20


Thanks again for your help :)




"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John")

Better to use cells to hold the criteria.

E2 = John
F2 = VS
G2 = VD

=SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2)

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and
B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned
using
the
simple
average formula. I'm guessing I need to nest something to not
count
nulls?
Help!!!






.

Thanks, let me try that :)

"T. Valko" wrote:

The survey its actually like this

So, you have a separate table with the code values?

Ok, rearange your code table so that it's sorted in ascending order like
this:

D...-100
NS...0
S...0
VD...-100
VS...100

Assume that table is in the range A1:B5

Your list of names and codes is in the range D1:E8.

Array entered** :

=AVERAGE(IF(D1:D8="John",LOOKUP(E1:E8,A1:B5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

Why is that its giving me a 0 value?

The survey its actually like this :

VS = 100
S = 0
NS = 0
VD = -100
D = -100

So if John has

John VS
John VD
John D
John S
John NS
Mike D
Mike VS
Mike VS


the formula again is

(VS + ( D + VD ))/Total number of surveys


So its like this

(100 + ( -200 )) / 5

So the answer for this should be -20


Thanks again for your help :)




"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(A2:A7="John"),--(ISNUMBER(MATCH(B2:B7,{"VS","VD"},0))),C2:C7)/COUNTIF(A2:A7,"John")

Better to use cells to hold the criteria.

E2 = John
F2 = VS
G2 = VD

=SUMPRODUCT(--(A2:A7=E2),--(ISNUMBER(MATCH(B2:B7,F2:G2,0))),C2:C7)/COUNTIF(A2:A7,E2)

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi There!

I Need your help again.

I have This

John VS 100
Mike VD -100
Mike VS 100
John VS 100
John VD -100
John S 0

I would like to compute the score of John, for him only

This is the Formula : ( VS + ( VD )) / Total number of Surveys

So its like this

(200 + ( -100 )) / 4


Thanks,

Hijosdelongi



"T. Valko" wrote:

I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Da ta!$C:$C,""))

You can't use entire columns as range references in array formulas in
Excel
2003. Use a smaller specific range.

Try it like this (array entered**)

=AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data !C1:C10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in
message
...
Hi,

I have a question for Excel 2003

=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Dat a!$C:$C,""))

Im trying to get the AVERAGE of this and its giving me a #VALUE!
error..
$A2
is the name that is suppose to be equal in the Data! worksheet and
B$1
is
the
date that is suppose to be equal in the Data! worksheet. Im trying
to
use
AND
in IF for me to have two logical test..

Can you help me with this?

Thank you so much!



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the
results.
It
figured 79% when it should have been 94%. 94% was returned
using
the
simple
average formula. I'm guessing I need to nest something to not
count
nulls?
Help!!!






.



.