Average Function Help Needed

**michaelas** · 09-11-2005, 04:01 PM

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

**BenjieLop** · 09-11-2005, 05:02 PM

<< 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** · 09-12-2005, 11:59 AM

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** · 09-12-2005, 03:29 PM

That did it! Thanks BenjieLop!

michaelas

**BenjieLop** · 09-12-2005, 08:19 PM

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

Regards.

KL · 09-11-2005, 05:05 PM

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" <michaelas.1v7nii_1126469130.8463@excelforum-nospam.com> wrote
in message news:michaelas.1v7nii_1126469130.8463@excelforum-nospam.com...
>
> 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
>

Ragdyer · 09-11-2005, 05:05 PM

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" <michaelas.1v7nii_1126469130.8463@excelforum-nospam.com> wrote
in message news:michaelas.1v7nii_1126469130.8463@excelforum-nospam.com...
>
> 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
>

**michaelas** · 09-11-2005, 11:58 PM

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

**BenjieLop** · 09-12-2005, 12:22 AM

Originally Posted by michaelas

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.

**michaelas** · 09-12-2005, 11:25 AM

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