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
  #51  
Old April 26th, 2010, 10:15 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can you AVERAGE IF and not null?

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(D18="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!!!






.



  #52  
Old April 26th, 2010, 11:48 PM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default Can you AVERAGE IF and not null?

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(D18="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!!!






.



.

 




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 06:25 PM.


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