Discussion Groups Home

Can you AVERAGE IF and not null? in Excel Worksheet Functions

5/5/2005 2:51 PM PST

Question

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

Was this post helpful to you?

Top

JE McGimpsey

5/5/2005 3:09 PM PST

Answer

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

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

In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
"Spottkitty" <Spottkitty@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!!!

Was this post helpful to you?

Top

spottkitty'

5/5/2005 3:31 PM PST

	Thank you SOOOOOOOOO Much!!!! Worked like a charm! "JE McGimpsey" wrote: > One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): > > =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45)) > >

Was this post helpful to you?

Top

Hijosdelongi

7/4/2009 2:24 PM PST

Hi,

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!

"JE McGimpsey" wrote:

> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>
> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
>
> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> "Spottkitty" <Spottkitty@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!!!
>

Was this post helpful to you?

Top

Dave Peterson

7/4/2009 2:37 PM PST

You can't use the entire column for array formulas in xl2003 and below.

(Remember to use ctrl-shift-enter, too)

Hijosdelongi wrote:
>
> Hi,
>
> 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!
>
> "JE McGimpsey" wrote:
>
> > One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >
> > =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >
> > In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> > "Spottkitty" <Spottkitty@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!!!
> >

--

Dave Peterson

Was this post helpful to you?

Top

Hijosdelongi

7/4/2009 2:51 PM PST

ic, but is the =AVERAGE(IF(AND( correct?

Thanks

"Dave Peterson" wrote:

> You can't use the entire column for array formulas in xl2003 and below.
>
> (Remember to use ctrl-shift-enter, too)
>
> Hijosdelongi wrote:
> >
> > Hi,
> >
> > 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!
> >
> > "JE McGimpsey" wrote:
> >
> > > One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> > >
> > > =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> > >
> > > In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> > > "Spottkitty" <Spottkitty@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!!!
> > >
>
> --
>
> Dave Peterson
>

Was this post helpful to you?

Top

T. Valko

7/4/2009 2:51 PM PST

>I have a question for Excel 2003
>=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> Hi,
>
> 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!
>
>
>
> "JE McGimpsey" wrote:
>
>> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>>
>> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
>>
>> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
>> "Spottkitty" <Spottkitty@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!!!
>>

Was this post helpful to you?

Top

Hijosdelongi

7/4/2009 3:12 PM PST

EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still no
scores for that they..

Thank you :)

"T. Valko" wrote:

> >I have a question for Excel 2003
> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> > Hi,
> >
> > 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!
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >>
> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >>
> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> >> "Spottkitty" <Spottkitty@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!!!
> >>
>
>
>

Was this post helpful to you?

Top

T. Valko

7/4/2009 3:43 PM PST

Try it like this:

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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:7138297D-BAA3-4EB4-8900-A64F9EC50E3F@microsoft.com...
> EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still
> no
> scores for that they..
>
> Thank you :)
>
>
> "T. Valko" wrote:
>
>> >I have a question for Excel 2003
>> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
>> > Hi,
>> >
>> > 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!
>> >
>> >
>> >
>> > "JE McGimpsey" wrote:
>> >
>> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>> >>
>> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
>> >>
>> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
>> >> "Spottkitty" <Spottkitty@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!!!
>> >>
>>
>>
>>

Was this post helpful to you?

Top

Hijosdelongi

7/4/2009 3:53 PM PST

Does ISERROR works in Excel 2003?

Thanks :)

"T. Valko" wrote:

> Try it like this:
>
> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:7138297D-BAA3-4EB4-8900-A64F9EC50E3F@microsoft.com...
> > EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still
> > no
> > scores for that they..
> >
> > Thank you :)
> >
> >
> > "T. Valko" wrote:
> >
> >> >I have a question for Excel 2003
> >> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> >> > Hi,
> >> >
> >> > 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!
> >> >
> >> >
> >> >
> >> > "JE McGimpsey" wrote:
> >> >
> >> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >> >>
> >> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >> >>
> >> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> >> >> "Spottkitty" <Spottkitty@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!!!
> >> >>
> >>
> >>
> >>
>
>
>

Was this post helpful to you?

Top

Hijosdelongi

7/4/2009 4:44 PM PST

I got the answer already, thank you so much!!!

=)

"T. Valko" wrote:

> Try it like this:
>
> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:7138297D-BAA3-4EB4-8900-A64F9EC50E3F@microsoft.com...
> > EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are still
> > no
> > scores for that they..
> >
> > Thank you :)
> >
> >
> > "T. Valko" wrote:
> >
> >> >I have a question for Excel 2003
> >> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> >> > Hi,
> >> >
> >> > 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!
> >> >
> >> >
> >> >
> >> > "JE McGimpsey" wrote:
> >> >
> >> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >> >>
> >> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >> >>
> >> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> >> >> "Spottkitty" <Spottkitty@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!!!
> >> >>
> >>
> >>
> >>
>
>
>

Was this post helpful to you?

Top

T. Valko

7/4/2009 7:13 PM PST

You're welcome!

--
Biff
Microsoft Excel MVP

"Hijosdelongi" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:DA647F5B-CA0E-4EE3-BA55-453959FF770D@microsoft.com...
>I got the answer already, thank you so much!!!
>
> =)
>
>
> "T. Valko" wrote:
>
>> Try it like this:
>>
>> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:7138297D-BAA3-4EB4-8900-A64F9EC50E3F@microsoft.com...
>> > EXCELENT! It worked!!! and how can I hide the #DIV/0! if there are
>> > still
>> > no
>> > scores for that they..
>> >
>> > Thank you :)
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> >I have a question for Excel 2003
>> >> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in
>> >> message
>> >> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
>> >> > Hi,
>> >> >
>> >> > 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!
>> >> >
>> >> >
>> >> >
>> >> > "JE McGimpsey" wrote:
>> >> >
>> >> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>> >> >>
>> >> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
>> >> >>
>> >> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
>> >> >> "Spottkitty" <Spottkitty@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!!!
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Was this post helpful to you?

Top

Hijosdelongi

7/12/2009 7:30 PM PST

Hi,

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

"T. Valko" wrote:

> >I have a question for Excel 2003
> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> > Hi,
> >
> > 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!
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >>
> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >>
> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> >> "Spottkitty" <Spottkitty@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!!!
> >>
>
>
>

Was this post helpful to you?

Top

Hijosdelongi

7/14/2009 10:50 PM PST

Hi T. Valko,

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.

"T. Valko" wrote:

> >I have a question for Excel 2003
> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
> > Hi,
> >
> > 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!
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
> >>
> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
> >>
> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
> >> "Spottkitty" <Spottkitty@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!!!
> >>
>
>
>

Was this post helpful to you?

Top

T. Valko

7/14/2009 10:59 PM PST

You'll have to post the formula.

--
Biff
Microsoft Excel MVP

"Hijosdelongi" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:C406F9A8-909D-4FFF-ACA3-2E3C25E7139E@microsoft.com...
> Hi T. Valko,
>
> 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.
>
>
>
> "T. Valko" wrote:
>
>> >I have a question for Excel 2003
>> >=AVERAGE(IF(AND($A2=Data!$B:$B,B$1=Data!$A:$A),Data!$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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:1567382F-DBDE-48B7-B3F4-A88D05CC5987@microsoft.com...
>> > Hi,
>> >
>> > 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!
>> >
>> >
>> >
>> > "JE McGimpsey" wrote:
>> >
>> >> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>> >>
>> >> =AVERAGE(IF((A3:A45="Photo")*(H3:H45<>""),H3:H45))
>> >>
>> >> In article <32A8DE13-A9BB-4B60-A7B1-F029DCB05F51@microsoft.com>,
>> >> "Spottkitty" <Spottkitty@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!!!
>> >>
>>
>>
>>

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies
			More...