Road Bike, Cycling Forums banner
1 - 9 of 9 Posts

·
What Would Google Do.
Joined
·
1,486 Posts
Discussion Starter · #1 ·
I have a spreadsheet downloaded from Bjarne Riis' win in the Amstel Gold Race, showwing all 6hrs of data for watts, spd, cadence, and heart rate from his SRM. For all you Excel guys, how do you make a function so that I can workout his avg watts...NOT including the Zero's. Normalised Power I think you call it?

Yeah so I can do the function for his average watts, but cant figure out how to do the average without the zeros (of which there are 1500 samples of the total of 4600 samples -each of 5 sec) so its making the average WAY lower than it would be without the zeros.

anyone???
 

·
Impulse Athletic Coaching
Joined
·
5,576 Posts
muscleendurance said:
I have a spreadsheet downloaded from Bjarne Riis' win in the Amstel Gold Race, showwing all 6hrs of data for watts, spd, cadence, and heart rate from his SRM. For all you Excel guys, how do you make a function so that I can workout his avg watts...NOT including the Zero's. Normalised Power I think you call it?

Yeah so I can do the function for his average watts, but cant figure out how to do the average without the zeros (of which there are 1500 samples of the total of 4600 samples -each of 5 sec) so its making the average WAY lower than it would be without the zeros.

anyone???
NP includes zeros. NP just calculates what your average power would have been given a steady-state nature. That file is all over the place and the numbers are out there somewhere. I would probably check the wattage forum.

Why wouldn't you want to include zeros? He rested during that time...
 

·
What Would Google Do.
Joined
·
1,486 Posts
Discussion Starter · #3 ·
iliveonnitro said:
Why wouldn't you want to include zeros? He rested during that time...
I suppose so..I guess Im just curious, and now its half about figuring out the solution to the problem rather than actually knowing his watts minus the zeros!
thanks though.
 

·
Registered
Joined
·
99 Posts
say the numbers are in cells A1 through A10:

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

this takes all the numbers is cells A1 through A10 and adds them together, then divides by the total number of data points that are greater than zero.
 

·
What Would Google Do.
Joined
·
1,486 Posts
Discussion Starter · #5 ·
asdasd44 said:
say the numbers are in cells A1 through A10:

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

this takes all the numbers is cells A1 through A10 and adds them together, then divides by the total number of data points that are greater than zero.
Tried that, but not working? (I substitued the A's ect for the B's in my spreadsheet ect)

Thanks! - have you actually got compound functions like that to work before yourself?
maybe its a version thing..I have Excel 2000!
 

·
What Would Google Do.
Joined
·
1,486 Posts
Discussion Starter · #7 · (Edited)
Aha! I got it! - still going cross eyed trying to work out what was wrong, maybe you typed a o instead of a 0 or something? anyway its accepted the function.
Unfortunately I cant give you the link, I downloaded it a couple of years ago, now idea of what site or anything, but if you google it you might get it?

But for helping me out heres the 'reward' if your interested, 6hr race avg 36km/hr (Recorded), 42km/hr (actual), avg heart rate 65%! watts 275w/311 without zeros, I guess he wasnt resting much afterall!

thanks again asdasd44 :thumbsup:
 

·
Registered
Joined
·
1,038 Posts
If I remember, It was in Velonews in spring of "96 or 97 when he won. Riis went on a long solo break but had a wheel or bike change so there was no data for the last few k's. a pretty consistant solo effort.
 

·
Cycling Coach
Joined
·
1,734 Posts
Non zero average power tells you very little of value.

If you want to calculate normalised power (which is significantly more useful), then:
1. calculate a 30-second rolling average power
2. raise each 30-sec average value to the 4th power
3. average all of those values raised to the 4th power
4. take the 4th root of that average

that is normalised power.
 
1 - 9 of 9 Posts
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top