Hey,
I'm creating a spreadsheat to monitor my workouts and have hit a niggle I can't find the code for. I've searched all over.
I have basically rows of infornmation from each workout covering time, distance, speed, heartrate and so on. I also have some boxes to the side that use the information from each field and show quick easy information for me such as my average speed over the period and so on.
The problem is I am trying to make it show average speed and have a simple =AVERAGE(C2:C9999) sum in the box. This is all well BUT I have information from short power sessions and longer mileage rides and would like to make the average ignore the low value distances.
Does anybody have an idea of a formular that would calculate an average using only the values over 50 for example? I've tried some stuff with the >50 and IF rules but can't hit one that works.
I would just tell it to look at specific values but it updates so often it would be nice to get it on auto!
Thanks in advance.
Last edited by samuelhorton; 09-20-2008 at 03:41 PM. Reason: Error in typing.
One option is to use this formula: =SUMPRODUCT((C2:C9999)*(D2:D9999>50))/SUMPRODUCT(--(D2:D9999>50))
Another option is to use Autofilter for all values >50 and a formula that says =SUBTOTAL(1,C2:C9999)
HTH
Ola
Firstly thankyou for the quick reply and the help.
Sorry, I'm a little lost as to where the D comes in... should they be C too?!
Also this gives me a value closer to the average of values over 50, but not actually it. It should be 77.29 and it's reading 59.25. Not sure what the seccond sugestion means to be honest.
Last edited by samuelhorton; 09-20-2008 at 05:22 PM.
try
=SUMIF(C1:C9999,">50")/COUNTIF(C1:C9999,">50")
I just get #NAME?
Nevermind, I've simply created a seperate columb just for mileage over 50... simple but effective...
This will be working for you
=SUMIF(C1:C9999,">50")/MAX(COUNTIF(C1:C9999,">50"),1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks