Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
<< 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The sum and average function view all numbers as zero?? | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
average range function | Excel Worksheet Functions | |||
average function in Excel 2002 | New Users to Excel | |||
average if function | Excel Worksheet Functions |