Can you AVERAGE IF and not null?

T

T. Valko

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 Edit>Go To>Special
Select: Formulas and uncheck everything *except* Errors
OK

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

Goto the menu Edit>Delete
Select: Entire Row
OK
 
H

Hijosdelongi

ahh ok, thanks dude =)


T. Valko said:
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 Edit>Go To>Special
Select: Formulas and uncheck everything *except* Errors
OK

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

Goto the menu Edit>Delete
Select: Entire Row
OK
Click to expand...
 
H

Hijosdelongi

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

T. Valko

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.
 
H

Hijosdelongi

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

T. Valko

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

Hijosdelongi

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
 
H

Hijosdelongi

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
 
H

Hijosdelongi

This actually works but is there any way it can calculate how many more 100's
a student needs to have for him/her to reach 72..?

ex,

jorge alrealy has an average of 70 out of 12 exams, so how many more 100's
he needs to get for him to get a 72+ average..


Thank you.

hijosdelongi
 
H

Hijosdelongi

Hi, its me again..

If ever you find the answer to my last Question, please do message me..
thank you so much!

hijosdelongi
 
H

Hijosdelongi

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

T. Valko

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
 
H

Hijosdelongi

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

T. Valko

=COUNT(IF((Data!A2:A100=B2)*(Data!B2:B100=C3),Data!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?
 
H

Hijosdelongi

for that i want to get the average of number of very satisfied rating versus
the non very satisfied rating of a single person..

ex.

for mike, he has 4 surveys and 2 out of 4 are very satisfied, so clearly its
50% right... thats what i mean for the average, i dont know how to formulate
that.

Thank you.
 
H

Hijosdelongi

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need to count/average if... 6
Average formula only works partially..... 6
Average of difference between dates greater than zero 8
Calculating average scores from multiple sheets' information 2
count if equal to not working? 1
Help requested for nested conditional formulas referencing other c 3
SUM, AVERAGE and PRINT with #N/A cells 11
Error Handling #N/A with AVERAGE Function - Average of values in Row 13

Top