Average Function Help Needed
 

Hi,

I'm trying to calculate average miles in my mileage log.
Col. F = total miles for each day
Col E = destination eg. store, supplyhouse, rec, auto, etc
I want to total all the "rec" miles and average them in a single cell.
Can anyone help? Thanks

Michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

Hi michaelas,

Try this ARRAY formula (confirm with Ctrl+Shift+Enter, not just Enter!!!):

=AVERAGE(IF((E2:E100="rec")*(F2:F100<""),F2:F100) )

Regards,
KL


"michaelas" wrote
in message ...

Hi,

I'm trying to calculate average miles in my mileage log.
Col. F = total miles for each day
Col E = destination eg. store, supplyhouse, rec, auto, etc
I want to total all the "rec" miles and average them in a single cell.
Can anyone help? Thanks

Michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile:
http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

A couple of non-array formulas:

Say data list is E2 to F100,
And you enter the destination you're looking to average into G1:

=SUMIF(E2:E100,G1,F2:F100)/COUNTIF(E2:E100,G1)

OR

=SUMPRODUCT((E2:E100=G1)*F2:F100)/COUNTIF(E2:E100,G1)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"michaelas" wrote
in message ...

Hi,

I'm trying to calculate average miles in my mileage log.
Col. F = total miles for each day
Col E = destination eg. store, supplyhouse, rec, auto, etc
I want to total all the "rec" miles and average them in a single cell.
Can anyone help? Thanks

Michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile:
http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

<< Hi,

I'm trying to calculate average miles in my mileage log.
Col. F = total miles for each day
Col E = destination eg. store, supplyhouse, rec, auto, etc

I want to total all the "rec" miles

=SUMIF(E1:E100,\"REC\",F1:F100)


<< and average them in a single cell.


=SUMIF(E1:E100,\"REC\",F1:F100)/COUNTIF(E1:E100,\"REC\")


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466654

Thanks BenjieLop for your reply & formula. It worked great!

Can you tell me how would I average all the daily _work_ miles if all
the destinations have different names?

michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

michaelas Wrote:
Thanks BenjieLop for your reply & formula. It worked great!

Can you tell me how would I average all the daily _work_ miles if all
the destinations have different names?

michaelas

Just change the "rec" in the formula with the destination of your
choice. I hope I understood your question correctly.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466654

What I want to do is average all the work miles _minus_ the Rec miles.
(Rec - recreation)

eg.
Col A-F:

5-Jul / Tue/ 64522/ 64542/ 20/ John - Meinecke St / Hayward
6-Jul/ Wed/ 64542/ 64562/ 20/ George St. - San Luis
7-Jul/ Thu/ 64562/ 64612/ 50/ John - Murray / Hayward Lumber/ De Cou
8-Jul Fri 64612 64690 78 Creston CA - Peg's deck
9-Jul Sat 64690 64776 86 Creston CA - Peg's deck
10-Jul Sun 64776 64790 14 Rec

I want to total all the work miles and compute the daily average, but
subtract all the rec (recreation) miles. But each work day may have
different names. Can I compute a formula to add up all the miles and
then subtract the Rec miles?

(In this eg. E= daily miles/F=destination. I had them switched around
in my original question - sorry)
Thanks for your help & replies, B. I hope this is clear what I want to
do.

michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

To determine the average of your non "rec" entries, simply change "rec"
to "<rec" so your formula will look like this:


=SUMIF(E1:E100,\"<REC\",F1:F100)/COUNTIF(E1:E100,\"<REC\")


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466654

That did it! Thanks BenjieLop!

michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=466654

You are welcome... I am just glad that I can help. Thanks for the
feedback.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466654