Interval planning function

[frost]

I have been missing a formula that could calculate the Normalized Power for a given interval workout and there's been some threads in the past about this issue too.

NP can be easily calculated with a simple spreadsheet formula if you forget about the smoothed average and use plain 30s average instead, which will give totally adequate accuracy but anyway I had some spare time (read, I really should start work but am still in summer vacation mood and motivation is lurking somewhere behind the corner ) so I decided to write a specific function for it.

Just copy+paste the code below to Excel VBA module (preferably in 'PERSONAL' workbook which makes it available to all workbooks) and use it as any Excel funtion.

Public Function PNorm(p1 As Double, t1 As Long, p2 As Double, t2 As Long, intCount As Long)
'p1 = interval power
't1 = interval time in seconds
'p2 = recovery power
't2 = recovery time in seconds
'intCount = number of intervals
'variable SWindowSize is the lenght of the smoothing window. Default value 30 but
' you can obviously change it.
'note! NP is calculated from the start of the first interval until the end of the last interval


Dim i, j, totalTime, SWindowSize, intervalTest As Long
Dim runningTotal, runningAverage, runningSqTotal As Double

SWindowSize = 30

totalTime = t1 * intCount + t2 * (intCount - 1)

For i = 1 To totalTime
j = i - SWindowSize
intervalTest = Int((i - (t1 + t2) * Int((i - 1) / (t1 + t2)) - 1) / t1)
If intervalTest = 0 Then
runningTotal = runningTotal + p1
Else
runningTotal = runningTotal + p2
End If

If i > SWindowSize Then
intervalTest = Int((j - (t1 + t2) * Int((j - 1) / (t1 + t2)) - 1) / t1)
If intervalTest = 0 Then
runningTotal = runningTotal - p1
Else
runningTotal = runningTotal - p2
End If
End If

runningAverage = runningTotal / SWindowSize
runningSqTotal = runningSqTotal + runningAverage ^ 4
Next

PNorm = (runningSqTotal / totalTime) ^ (1 / 4)

End Function

 

[giannip]

I have been missing a formula that could calculate the Normalized Power for a given interval workout and there's been some threads in the past about this issue too.

NP can be easily calculated with a simple spreadsheet formula if you forget about the smoothed average and use plain 30s average instead, which will give totally adequate accuracy but anyway I had some spare time (read, I really should start work but am still in summer vacation mood and motivation is lurking somewhere behind the corner ) so I decided to write a specific function for it.

Just copy+paste the code below to Excel VBA module (preferably in 'PERSONAL' workbook which makes it available to all workbooks) and use it as any Excel funtion.

do you mean for intervals shorter than 5min or for people without TrainingPeaks?

Hope so or I've missed something

 

[frost]

do you mean for intervals shorter than 5min or for people without TrainingPeaks?

Hope so or I've missed something

Yes, you did. Emphasis on word Planning

Maybe I wasn't clear enough: This is to plan eg. a L5 workout and estimate if it is doable from the NP point of view.

 

[giannip]

Yes, you did. Emphasis on word Planning

Maybe I wasn't clear enough: This is to plan eg. a L5 workout and estimate if it is doable from the NP point of view.

yes.... planning.............

 

[giannip]

Yes, you did. Emphasis on word Planning

Maybe I wasn't clear enough: This is to plan eg. a L5 workout and estimate if it is doable from the NP point of view.

Very nice by the way...

 

[giannip]

Very nice by the way...

If anyone wants a web based version I "borrowed" the above code and made a page for anyone to use.

Calculate the Normalized Power for a given interval workout

 

[Alex Simmons]

If anyone wants a web based version I "borrowed" the above code and made a page for anyone to use.

Calculate the Normalized Power for a given interval workout

I might be being picky but I just tested it against a square wave power input:
4-min @ 400W
4-min @ 200W
x 6

This web page calculator gives me an NP of 344W, when the actual NP for a power file like that is 339W.

I was going to test another sample but it crashes after the first time you use it.

 

[frenchyge]

This web page calculator gives me an NP of 344W, when the actual NP for a power file like that is 339W.

I havn't checked the math, but it appears that the calculator presented here snips the rest period off the last interval of the set, as mine does here at home. The last rest doesn't really contribute to making the workout more feasible, and I go straight into cooldown after the last interval anyway.

 

[giannip]

I might be being picky but I just tested it against a square wave power input:
4-min @ 400W
4-min @ 200W
x 6

This web page calculator gives me an NP of 344W, when the actual NP for a power file like that is 339W.

I was going to test another sample but it crashes after the first time you use it.

I'm working off the above formula. Do you want to forward yours ? I can try build that in.

Re: the crash, this could be the previous version I was working on. Let me know if it's still happening.

 

[frost]

it appears that the calculator presented here snips the rest period off the last interval of the set

Correct. I was thinking of putting an optional argument to control if last rest period should be included or not but then skipped it as for the planning purpose the interval parts is of interest.

Anyway it is not a commercial product. I did it in about 15 minutes so if you find any bugs or errors in it, it is not being picky telling about it.

I haven't have any crasing issues. Alex, which office version do you use? Does it get stuck or do you get some error message?

 

[frost]

If you want to include the last rest period to NP calculation change the following line:

totalTime = t1 * intCount + t2 * (intCount - 1)

to

totalTime = t1 * intCount + t2 * intCount

it gives 338,25 to the Alex' example. Minor difference could be explained by the way the first 30s is calculated. I do not know how it is actually handled in real NP calculation so I made a shortcut and just summed each second before 30 second and divided by smoothing window 30 which should probably be divided by the number of seconds.

You may change the line:

runningAverage = runningTotal / SWindowSize

to

If i < SWindowSize Then
runningAverage = runningTotal / i
else
runningAverage = runningTotal / SWindowSize
End if

 

[giannip]

I haven't have any crasing issues. Alex, which office version do you use? Does it get stuck or do you get some error message?

No the crashing was for the web page I created (it's fixed now) based on your formula. Hope that's ok. I though it might be nice for more people to have access to it as it's pretty handy.

Calculate the Normalized Power for a given interval workout

 

[frost]

No the crashing was for the web page I created (it's fixed now) based on your formula. Hope that's ok. I though it might be nice for more people to have access to it as it's pretty handy.

Calculate the Normalized Power for a given interval workout

Ok, I just read your message about the web page after I had written my post.

 

[giannip]

If you want to include the last rest period to NP calculation change the following line:

totalTime = t1 * intCount + t2 * (intCount - 1)

to

totalTime = t1 * intCount + t2 * intCount

it gives 338,25 to the Alex' example. Minor difference could be explained by the way the first 30s is calculated. I do not know how it is actually handled in real NP calculation so I made a shortcut and just summed each second before 30 second and divided by smoothing window 30 which should probably be divided by the number of seconds.

You may change the line:

runningAverage = runningTotal / SWindowSize

to

If i < SWindowSize Then
runningAverage = runningTotal / i
else
runningAverage = runningTotal / SWindowSize
End if

I've updated the site with the above changes .

I am rounding off so the result for Alex's example is 340 (339.59)

 

[frost]

I've updated the site with the above changes .

I am rounding off so the result for Alex's example is 340 (339.59)

That's nice!! Maybe you could still add an argument if the last recovery should be included or not and control this with it:

totalTime = t1 * intCount + t2 * (intCount - 1)

to

totalTime = t1 * intCount + t2 * intCount

 

[giannip]

That's nice!! Maybe you could still add an argument if the last recovery should be included or not and control this with it:

totalTime = t1 * intCount + t2 * (intCount - 1)

to

totalTime = t1 * intCount + t2 * intCount

like so? Calculate the Normalized Power for a given interval workout

would people prefer the rounded off figure ?

 

[frenchyge]

would people prefer the rounded off figure ?

Nawwww.... I like my workout estimators accurate to at least 3 digits.

How about that typo, though?

 

[Squint]

I might be being picky but I just tested it against a square wave power input:
4-min @ 400W
4-min @ 200W
x 6

This web page calculator gives me an NP of 344W, when the actual NP for a power file like that is 339W.

It is indeed 339W NP: View attachment 10847

 

[steve]

it is indeed 339w np:

Hi there

Instead of post wide images that mess up the thread layout, you can use a neat attachment function that will display your image as a thumbnail within the post text.

So do it, simply click the

icon and upload your image(s), close the popup window once complete.

Now the same icon

will have a drop down menu, this allows you to select your images and they will be inserted into your post (where your cursor is positioned).

View attachment 10846

Text can be displayed under attachments managed this way.

regards

 

[Piotr]

like so? Calculate the Normalized Power for a given interval workout

would people prefer the rounded off figure ?

Hey, I took that "That's too easy" personally. Should you be asking for one's FTP before making such judgements?