=AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
In article <32A8DE13-A9BB-4B60...@microsoft.com>,
"Spottkitty" <Spott...@discussions.microsoft.com> 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!!!
I have a question for Excel 2003
=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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!
(Remember to use ctrl-shift-enter, too)
--
Dave Peterson
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:1567382F-DBDE-48B7...@microsoft.com...
Thanks
Thank you :)
Array entered.
=IF(ISERROR(AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data!C1:C10))),"",AVERAGE(IF((Data!A1:A10=B1)*(Data!B1:B10=A2),Data!C1:C10)))
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:7138297D-BAA3-4EB4...@microsoft.com...
Thanks :)
=)
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:DA647F5B-CA0E-4EE3...@microsoft.com...
I have a Question.. is VLOOKUP plus IF possible? This is my fomula..
=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1),....
is my logical tests or conditions correct? and how will i put the VLOOKUP
codes?
Can you help me with this..
THank you so much
=average(if(jundata!b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,novdata!b2,decdatab2<>""),jundata!b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,novdata!b2,decdata!b2)
--
Biff
Microsoft Excel MVP
"klic33" <kli...@discussions.microsoft.com> wrote in message
news:F1C8F4B9-2F42-4520...@microsoft.com...
How are you?
Got a Question again... im trying to get a data from the database using
VLOOKUP and why is that even though there is no value in the database it
still displays the 0 value?
And can you teach mo how to get a value from the database even though theres
no value in it?
ex.
A1 = "null value or no value"
how will display a value that is equivalent to a text or number even though
theres no value in the database?
Thank you.
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:C406F9A8-909D-4FFF...@microsoft.com...
Create these named formulas...
Insert>Name>Define
Name: SumSheets
Refers to:
=SUMIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\data")&"!B2"),">0")
Name: CountSheets
Refers to:
=COUNTIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\data")&"!B2"),">0")
Then, you average formula is:
=SUMPRODUCT(SumSheets)/SUMPRODUCT(CountSheets)
Explanation:
Excel doesn't support *conditional* averaging across multiple sheets so we
need to trick it into doing so. We can't use the AVERAGE function in this
case. Since an average is the sum divided by the count that's what we're
doing with the above formula(s).
If you want to take the "easy" way out on this, on each sheet in the same
cell enter a formula like this:
=IF(B2>0,B2,"")
Let's assume those formulas are in cell B3. Then, you can use the AVAERAGE
function like this:
=AVERAGE(jundata:decdata!B3)
--
Biff
Microsoft Excel MVP
"klic33" <kli...@discussions.microsoft.com> wrote in message
news:1B9C9986-0850-49D0...@microsoft.com...
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..
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
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:BBABF7B6-7DE8-410B...@microsoft.com...
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:ED620B35-5B50-4C14...@microsoft.com...
ex.
A1 = 80% and can we have a comment on that, that automatically that says
"Passed"
Thanks.
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:2EDCB8D1-D607-4594...@microsoft.com...
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:1850E3D4-C17C-4CE8...@microsoft.com...
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
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
=IF(B1>=72,"",72-B1)
Copy down as needed.
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:8931F575-53EA-403E...@microsoft.com...
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
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:2A900A9E-9D89-4C06...@microsoft.com...
If ever you find the answer to my last Question, please do message me..
thank you so much!
hijosdelongi
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. :)
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:5EA840C2-03C1-43A9...@microsoft.com...
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!
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:CD45A172-957E-4681...@microsoft.com...
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.
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.
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:75F48818-0374-4997...@microsoft.com...
--
Biff
Microsoft Excel MVP
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:C3B250F7-4A9E-472C...@microsoft.com...
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!
=)
=INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A$2:$A$200=A2)*(Sheet1!$B$2:$B$200=B2),0))
--
HTH
Bob
"Hijosdelongi" <Hijosd...@discussions.microsoft.com> wrote in message
news:84044DDD-7E10-4ED7...@microsoft.com...
=)
"Bob Phillips" wrote:
> .
>
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
=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" <Hijosd...@discussions.microsoft.com> wrote in message
news:61C58123-09AF-417E...@microsoft.com...
"T. Valko" wrote:
> .
>
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:
> .
>
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" <Hijosd...@discussions.microsoft.com> wrote in message
news:751AD37A-6642-4E3D...@microsoft.com...
"T. Valko" wrote:
> .
>