Average AM or PM values

D

David

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?
 
C

Chip Pearson

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
 
F

Fred Smith

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

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

Elkar

Try this:

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

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

HTH,
Elkar
 
P

Peo Sjoblom

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
 
P

Peo Sjoblom

Nevermind, I misunderstood

Peo


Peo Sjoblom said:
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
Click to expand...
 
D

David

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

=sumif(C:C,"AM",A:A)/countif(A:A,"AM")
Click to expand...

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

David

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 {}.
Click to expand...

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

David

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