Average AM or PM values
 

ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

David,

Try the following array formula:

=AVERAGE(IF(A1:A5="am",C1:C5,FALSE))

Change the ranges to suit your needs. Since this is an array
formula, you must press CTRL+SHIFT+ENTER rather than just ENTER
when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed
in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"David" wrote in message
...
ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

You should be able to use Sum/Count, as in:

=sumif(C:C,"AM",A:A)/countif(A:A,"AM")

--
Regards,
Fred


"David" wrote in message
...
ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

Try this:

=SUMIF(A1:A100,"AM",C1:C100)/COUNTIF(A1:A100,"AM")

=SUMIF(A1:A100,"PM",C1:C100)/COUNTIF(A1:A100,"PM")

HTH,
Elkar

"David" wrote:

ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

Are these real times that excel sees at time, if so

=AVERAGE(IF(C1:C30<=TIME(11,59,59),C1:C30))

for AM

=AVERAGE(IF(C1:C30=TIME(12,,),C1:C30))


for PM

both entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"David" wrote in message
...
ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

Nevermind, I misunderstood

Peo


"Peo Sjoblom" wrote in message
...
Are these real times that excel sees at time, if so

=AVERAGE(IF(C1:C30<=TIME(11,59,59),C1:C30))

for AM

=AVERAGE(IF(C1:C30=TIME(12,,),C1:C30))


for PM

both entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"David" wrote in message
...
ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

Fred Smith wrote

You should be able to use Sum/Count, as in:

=sumif(C:C,"AM",A:A)/countif(A:A,"AM")


I had to change it to:
=SUMIF(A:A,"AM",C:C)/COUNTIF(A:A,"AM")
changing "AM" to "PM" for second forumla

I chose your solution because the range will grow.
Many thanks.

--
David

Average AM or PM values
 

Chip Pearson wrote

David,

Try the following array formula:

=AVERAGE(IF(A1:A5="am",C1:C5,FALSE))

Change the ranges to suit your needs. Since this is an array
formula, you must press CTRL+SHIFT+ENTER rather than just ENTER
when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed
in curly braces {}.



Yep, that works for a fixed range, but I will be adding to the range, so I
chose Fred's solution. Thanks for your help.

--
David

Average AM or PM values
 

Easily changed to match my "fixed" version of Fred's offering to allow for
unlimited expansion. Many thanks.

--
David

?B?RWxrYXI=?= wrote

Subject: Average AM or PM values
From: ?B?RWxrYXI=?=
Newsgroups: microsoft.public.excel.misc

Try this:

=SUMIF(A1:A100,"AM",C1:C100)/COUNTIF(A1:A100,"AM")

=SUMIF(A1:A100,"PM",C1:C100)/COUNTIF(A1:A100,"PM")

HTH,
Elkar

"David" wrote:

ColumnA alternates between text "AM" and "PM"
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average "AM" values
2) Average "PM" values

Should be simple, but I can't get it right.
Any help?

--
David

Average AM or PM values
 

I'm glad it worked for you. Sorry about my dyslexic Sumif function.

--
Regards,
Fred


"David" wrote in message
...
Fred Smith wrote

You should be able to use Sum/Count, as in:

=sumif(C:C,"AM",A:A)/countif(A:A,"AM")


I had to change it to:
=SUMIF(A:A,"AM",C:C)/COUNTIF(A:A,"AM")
changing "AM" to "PM" for second forumla

I chose your solution because the range will grow.
Many thanks.

--
David