software

ColumnA alternates between text quot;AMquot; and quot;PMquot;
ColumnC contains corresponding numbers/values
I want two formulas to put in Column E:
1) Average quot;AMquot; values
2) Average quot;PMquot; values

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

--
David

David,

Try the following array formula:

=AVERAGE(IF(A1:A5=quot;amquot;,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

quot;Davidquot; gt; wrote in message
...
gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt; ColumnC contains corresponding numbers/values
gt; I want two formulas to put in Column E:
gt; 1) Average quot;AMquot; values
gt; 2) Average quot;PMquot; values
gt;
gt; Should be simple, but I can't get it right.
gt; Any help?
gt;
gt; --
gt; David
You should be able to use Sum/Count, as in:

=sumif(C:C,quot;AMquot;,A:A)/countif(A:A,quot;AMquot;)

--
Regards,
Fredquot;Davidquot; gt; wrote in message
...
gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt; ColumnC contains corresponding numbers/values
gt; I want two formulas to put in Column E:
gt; 1) Average quot;AMquot; values
gt; 2) Average quot;PMquot; values
gt;
gt; Should be simple, but I can't get it right.
gt; Any help?
gt;
gt; --
gt; David
Try this:

=SUMIF(A1:A100,quot;AMquot;,C1:C100)/COUNTIF(A1:A100,quot;AMquot;)

=SUMIF(A1:A100,quot;PMquot;,C1:C100)/COUNTIF(A1:A100,quot;PMquot;)

HTH,
Elkar

quot;Davidquot; wrote:

gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt; ColumnC contains corresponding numbers/values
gt; I want two formulas to put in Column E:
gt; 1) Average quot;AMquot; values
gt; 2) Average quot;PMquot; values
gt;
gt; Should be simple, but I can't get it right.
gt; Any help?
gt;
gt; --
gt; David
gt;

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

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

for AM

=AVERAGE(IF(C1:C30gt;=TIME(12,,),C1:C30))for PM

both entered with ctrl shift amp; enter--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Davidquot; gt; wrote in message
...
gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt; ColumnC contains corresponding numbers/values
gt; I want two formulas to put in Column E:
gt; 1) Average quot;AMquot; values
gt; 2) Average quot;PMquot; values
gt;
gt; Should be simple, but I can't get it right.
gt; Any help?
gt;
gt; --
gt; David
Nevermind, I misunderstood

Peoquot;Peo Sjoblomquot; gt; wrote in message
...
gt; Are these real times that excel sees at time, if so
gt;
gt; =AVERAGE(IF(C1:C30lt;=TIME(11,59,59),C1:C30))
gt;
gt; for AM
gt;
gt; =AVERAGE(IF(C1:C30gt;=TIME(12,,),C1:C30))
gt;
gt;
gt; for PM
gt;
gt; both entered with ctrl shift amp; enter
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Davidquot; gt; wrote in message
gt; ...
gt;gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt;gt; ColumnC contains corresponding numbers/values
gt;gt; I want two formulas to put in Column E:
gt;gt; 1) Average quot;AMquot; values
gt;gt; 2) Average quot;PMquot; values
gt;gt;
gt;gt; Should be simple, but I can't get it right.
gt;gt; Any help?
gt;gt;
gt;gt; --
gt;gt; David
gt;
gt;
Fred Smith wrote

gt; You should be able to use Sum/Count, as in:
gt;
gt; =sumif(C:C,quot;AMquot;,A:A)/countif(A:A,quot;AMquot;)
gt;

I had to change it to:
=SUMIF(A:A,quot;AMquot;,C:C)/COUNTIF(A:A,quot;AMquot;)
changing quot;AMquot; to quot;PMquot; for second forumla

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

--
David

Chip Pearson wrote

gt; David,
gt;
gt; Try the following array formula:
gt;
gt; =AVERAGE(IF(A1:A5=quot;amquot;,C1:C5,FALSE))
gt;
gt; Change the ranges to suit your needs. Since this is an array
gt; formula, you must press CTRL SHIFT ENTER rather than just ENTER
gt; when you first enter the formula and whenever you edit it later.
gt; If you do this properly, Excel will display the formula enclosed
gt; in curly braces {}.
gt;
gt;

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

Easily changed to match my quot;fixedquot; version of Fred's offering to allow for
unlimited expansion. Many thanks.

--
David

=?Utf-8?B?RWxrYXI=?= wrote

gt; Subject: Average AM or PM values
gt; From: =?Utf-8?B?RWxrYXI=?= gt;
gt; Newsgroups: microsoft.public.excel.misc
gt;
gt; Try this:
gt;
gt; =SUMIF(A1:A100,quot;AMquot;,C1:C100)/COUNTIF(A1:A100,quot;AMquot;)
gt;
gt; =SUMIF(A1:A100,quot;PMquot;,C1:C100)/COUNTIF(A1:A100,quot;PMquot;)
gt;
gt; HTH,
gt; Elkar
gt;
gt; quot;Davidquot; wrote:
gt;
gt;gt; ColumnA alternates between text quot;AMquot; and quot;PMquot;
gt;gt; ColumnC contains corresponding numbers/values
gt;gt; I want two formulas to put in Column E:
gt;gt; 1) Average quot;AMquot; values
gt;gt; 2) Average quot;PMquot; values
gt;gt;
gt;gt; Should be simple, but I can't get it right.
gt;gt; Any help?
gt;gt;
gt;gt; --
gt;gt; David

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

--
Regards,
Fredquot;Davidquot; gt; wrote in message
...
gt; Fred Smith wrote
gt;
gt;gt; You should be able to use Sum/Count, as in:
gt;gt;
gt;gt; =sumif(C:C,quot;AMquot;,A:A)/countif(A:A,quot;AMquot;)
gt;gt;
gt;
gt; I had to change it to:
gt; =SUMIF(A:A,quot;AMquot;,C:C)/COUNTIF(A:A,quot;AMquot;)
gt; changing quot;AMquot; to quot;PMquot; for second forumla
gt;
gt; I chose your solution because the range will grow.
gt; Many thanks.
gt;
gt; --
gt; David