A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can you AVERAGE IF and not null?



 
 
Thread Tools Display Modes
  #21  
Old July 24th, 2009, 05:07 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase or
remove an entire row if a specific cell doesnt have any value or an error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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






  #22  
Old July 24th, 2009, 07:11 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

ahh ok, thanks dude =)


"T. Valko" wrote:

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase or
remove an entire row if a specific cell doesnt have any value or an error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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







  #23  
Old July 25th, 2009, 02:54 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

You're welcome!

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
ahh ok, thanks dude =)


"T. Valko" wrote:

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase
or
remove an entire row if a specific cell doesnt have any value or an
error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres
no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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









  #24  
Old August 2nd, 2009, 12:39 PM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

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




  #25  
Old August 2nd, 2009, 04:25 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

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






  #26  
Old August 4th, 2009, 04:44 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

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







  #27  
Old August 4th, 2009, 05:35 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

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









  #28  
Old August 22nd, 2009, 06:38 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

Hi, need some help again..

I have this project and i'm calculating the average of my students..

I have this table..

A B C

1 Mike 60 ?

2 Jorge 70 ?

3 Stan 65 ?

I would like to ask whats the formula to calculate how much more a student
needs to have for him/her to get 72? 72 is the passing score, and i would
like to ask how much he needs to reach 72. Possible score for a student to
have is between 0-100.

Thanks for you usual help.

hijosdelongi





"T. Valko" wrote:

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase or
remove an entire row if a specific cell doesnt have any value or an error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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







  #29  
Old August 22nd, 2009, 06:54 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

Hi, need some help again..

I have this project and i'm calculating the average of my students..

I have this table..

A B C

1 Mike 60 ?

2 Jorge 70 ?

3 Stan 65 ?

I would like to ask whats the formula to calculate how much more a student
needs to have for him/her to get 72? 72 is the passing score, and i would
like to ask how much he needs to reach 72. Possible score for a student to
have is between 0-100.

Thanks for you usual help.

hijosdelongi

"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
ahh ok, thanks dude =)


"T. Valko" wrote:

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase
or
remove an entire row if a specific cell doesnt have any value or an
error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres
no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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










  #30  
Old August 22nd, 2009, 07:14 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

Try this:

=IF(B1=72,"",72-B1)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi, need some help again..

I have this project and i'm calculating the average of my students..

I have this table..

A B C

1 Mike 60 ?

2 Jorge 70 ?

3 Stan 65 ?

I would like to ask whats the formula to calculate how much more a student
needs to have for him/her to get 72? 72 is the passing score, and i would
like to ask how much he needs to reach 72. Possible score for a student to
have is between 0-100.

Thanks for you usual help.

hijosdelongi





"T. Valko" wrote:

No, a formula can't do that. If you want it to be "semi-automatic" you'd
need a macro.

You can do it with a few clicks of a mouse...

Remove the error trap from your formula and let the errors generate.
Select column E
Goto the menu EditGo ToSpecial
Select: Formulas and uncheck everything *except* Errors
OK

That will select all the cells in col E that contain errors

Goto the menu EditDelete
Select: Entire Row
OK

--
Biff
Microsoft Excel MVP


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

Got a question again, is there any formula that can automatically erase
or
remove an entire row if a specific cell doesnt have any value or an
error
value to it???

ex.

I have this vlookup value in column E
=IF(ISERROR(VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)) ,"",VLOOKUP(A1:A5,Sheet1!B1:E100,3,FALSE)))

Is there anyway that i can delete or remove the entire row 4 if theres
no
returned value or an error value???

A B C D E
1 x g e e sharon
2 x as vf v sharon
3 y g h j david
4 f b a r
5 p r e f dexter

Thank you..


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









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.