(Msg. 1) Posted: Thu Dec 18, 2008 10:48 am
Post subject: Average If Statement (Excel 2003) Archived from groups: microsoft>public>excel>misc (more info?)
I am trying to find the formula for averaging cells in column B if the data
in Ais (a specific criteria). I've tried some formulas in the threads
related to average, but nothing seems to work.
Example:
If A1:A110 equals 01
Then Average B1:B110 (cells related to the criteria in A1:A110)
When using formulas, should cells have a specific format? Because there is
text involved, I set the cell format to general. Do you suggest differently?
Also, I'm working with data that I exported from a Microsoft Access 2007
database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 2) Posted: Thu Dec 18, 2008 11:06 am
Post subject: RE: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=AVERAGE(IF(A1:A100=1,B1:B100,""))
this is an array formula that must be enter with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200821
"--Viewpoint" wrote:
> I am trying to find the formula for averaging cells in column B if the data
> in Ais (a specific criteria). I've tried some formulas in the threads
> related to average, but nothing seems to work.
>
> Example:
> If A1:A110 equals 01
> Then Average B1:B110 (cells related to the criteria in A1:A110)
>
> When using formulas, should cells have a specific format? Because there is
> text involved, I set the cell format to general. Do you suggest differently?
> Also, I'm working with data that I exported from a Microsoft Access 2007
> database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 3) Posted: Thu Dec 18, 2008 11:08 am
Post subject: RE: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
An alternative to an array formula would be to use sumif divided by countif:
=sumif(a1:a110,"01",b1:b110)/countif(a1:a110,"01")
"--Viewpoint" wrote:
> I am trying to find the formula for averaging cells in column B if the data
> in Ais (a specific criteria). I've tried some formulas in the threads
> related to average, but nothing seems to work.
>
> Example:
> If A1:A110 equals 01
> Then Average B1:B110 (cells related to the criteria in A1:A110)
>
> When using formulas, should cells have a specific format? Because there is
> text involved, I set the cell format to general. Do you suggest differently?
> Also, I'm working with data that I exported from a Microsoft Access 2007
> database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 4) Posted: Thu Dec 18, 2008 11:51 am
Post subject: RE: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thank you.
"Gary''s Student" wrote:
> =AVERAGE(IF(A1:A100=1,B1:B100,""))
> this is an array formula that must be enter with CNTRL-SHFT-ENTER rather
> than just the ENTER key.
> --
> Gary''s Student - gsnu200821
>
>
> "--Viewpoint" wrote:
>
> > I am trying to find the formula for averaging cells in column B if the data
> > in Ais (a specific criteria). I've tried some formulas in the threads
> > related to average, but nothing seems to work.
> >
> > Example:
> > If A1:A110 equals 01
> > Then Average B1:B110 (cells related to the criteria in A1:A110)
> >
> > When using formulas, should cells have a specific format? Because there is
> > text involved, I set the cell format to general. Do you suggest differently?
> > Also, I'm working with data that I exported from a Microsoft Access 2007
> > database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 5) Posted: Thu Dec 18, 2008 11:53 am
Post subject: RE: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks. I actually tried this formula before posting my message and it
didn't work. However, I forgot that I had linked data on my worksheet and
that I had to enter the sheet name into the formula.
"bpeltzer" wrote:
> An alternative to an array formula would be to use sumif divided by countif:
> =sumif(a1:a110,"01",b1:b110)/countif(a1:a110,"01")
>
> "--Viewpoint" wrote:
>
> > I am trying to find the formula for averaging cells in column B if the data
> > in Ais (a specific criteria). I've tried some formulas in the threads
> > related to average, but nothing seems to work.
> >
> > Example:
> > If A1:A110 equals 01
> > Then Average B1:B110 (cells related to the criteria in A1:A110)
> >
> > When using formulas, should cells have a specific format? Because there is
> > text involved, I set the cell format to general. Do you suggest differently?
> > Also, I'm working with data that I exported from a Microsoft Access 2007
> > database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 6) Posted: Thu Dec 18, 2008 1:59 pm
Post subject: Re: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
See if this works:
Array entered** :
=AVERAGE(IF(A1:A110="01",B1:B110))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
The problem is more than likely the 01. Excel doesn't recognize leading
zeros as part of a numeric string. In order to get Excel to display leading
zeros you either have to apply a custom number format or the entry has to be
a TEXT string. In this case I'm "guessing" that 01 is a TEXT string.
--
Biff
Microsoft Excel MVP
"--Viewpoint" wrote in message
>I am trying to find the formula for averaging cells in column B if the data
> in Ais (a specific criteria). I've tried some formulas in the threads
> related to average, but nothing seems to work.
>
> Example:
> If A1:A110 equals 01
> Then Average B1:B110 (cells related to the criteria in A1:A110)
>
> When using formulas, should cells have a specific format? Because there
> is
> text involved, I set the cell format to general. Do you suggest
> differently?
> Also, I'm working with data that I exported from a Microsoft Access 2007
> database (SQL) but I'm using Excel 2003. Will this cause a problem?
(Msg. 7) Posted: Thu Dec 18, 2008 1:59 pm
Post subject: Re: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks. I did have that formula and it didn't work but once you wrote it, I
realized that the data was linked and that I had to add the sheet name.
Once more question: Using the same formula, rather than using the equal
sign, I want to select a group that contains PO1 or P02. How can I do that?
"T. Valko" wrote:
> See if this works:
>
> Array entered** :
>
> =AVERAGE(IF(A1:A110="01",B1:B110))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
> The problem is more than likely the 01. Excel doesn't recognize leading
> zeros as part of a numeric string. In order to get Excel to display leading
> zeros you either have to apply a custom number format or the entry has to be
> a TEXT string. In this case I'm "guessing" that 01 is a TEXT string.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "--Viewpoint" wrote in message
> > >I am trying to find the formula for averaging cells in column B if the data
> > in Ais (a specific criteria). I've tried some formulas in the threads
> > related to average, but nothing seems to work.
> >
> > Example:
> > If A1:A110 equals 01
> > Then Average B1:B110 (cells related to the criteria in A1:A110)
> >
> > When using formulas, should cells have a specific format? Because there
> > is
> > text involved, I set the cell format to general. Do you suggest
> > differently?
> > Also, I'm working with data that I exported from a Microsoft Access 2007
> > database (SQL) but I'm using Excel 2003. Will this cause a problem?
>
>
>
(Msg. 8) Posted: Thu Dec 18, 2008 4:01 pm
Post subject: Re: Average If Statement (Excel 2003) [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"--Viewpoint" wrote in message
> Thanks. I did have that formula and it didn't work but once you wrote it,
> I
> realized that the data was linked and that I had to add the sheet name.
>
> Once more question: Using the same formula, rather than using the equal
> sign, I want to select a group that contains PO1 or P02. How can I do
> that?
>
> "T. Valko" wrote:
>
>> See if this works:
>>
>> Array entered** :
>>
>> =AVERAGE(IF(A1:A110="01",B1:B110))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER)
>>
>> The problem is more than likely the 01. Excel doesn't recognize leading
>> zeros as part of a numeric string. In order to get Excel to display
>> leading
>> zeros you either have to apply a custom number format or the entry has to
>> be
>> a TEXT string. In this case I'm "guessing" that 01 is a TEXT string.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "--Viewpoint" wrote in message
>> >> >I am trying to find the formula for averaging cells in column B if the
>> >data
>> > in Ais (a specific criteria). I've tried some formulas in the threads
>> > related to average, but nothing seems to work.
>> >
>> > Example:
>> > If A1:A110 equals 01
>> > Then Average B1:B110 (cells related to the criteria in A1:A110)
>> >
>> > When using formulas, should cells have a specific format? Because
>> > there
>> > is
>> > text involved, I set the cell format to general. Do you suggest
>> > differently?
>> > Also, I'm working with data that I exported from a Microsoft Access
>> > 2007
>> > database (SQL) but I'm using Excel 2003. Will this cause a problem?
>>
>>
>>
All times are: Eastern Time (US & Canada) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in
Office