Average Function Help Needed

[michaelas]

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

Michaela

 

[KL]

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

 

[Ragdyer]

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)

 

[BenjieLop]

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

 

[michaelas]

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

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

michaela

 

[BenjieLop]

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

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

michaelas

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

Regards

 

[michaelas]

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, bu
subtract all the rec (recreation) miles. But each work day may hav
different names. Can I compute a formula to add up all the miles an
then subtract the Rec miles?

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

michaela

 

[BenjieLop]

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

 

[michaelas]

That did it! Thanks BenjieLop!

michaelas

 

[BenjieLop]

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

Regards.