In the cell that you want power displayed (ie B1):

=B2^3*0.019168+B2*5.24482

where B2 is the cell you are putting you speed.

No solving required

---

"...the street finds its own uses for things"

Use the "Goal Seek" feature if the equation cannot be solved explicitly.

---

---

He knows power, but doesn't know speed.

---

Swimming Workout of the Day:

Favourite Swim Sets:

2020 National Masters Champion - M50-54 - 50m Butterfly

Cubic equations are tricky to solve analytically. I plugged it into Wolfram Alpha and this was the only real solution for speed as a function of power:




Another option is to use the "Solver" add-in in Excel.

---

CodyBeals.com | Instagram | TikTok
ASICS | Ventum | Martin's | HED | VARLO | Shimano | 4iiii | Keystone Communications

This is a really fun question. I ran into it myself when I wrote some code to solve for power given speed. Easy!

Then started trying to do the algebra to do the reverse and was like WAAAAT THE FFFUUU

I just do a binary search for the answer. Which is, I learned later, a ghetto Newton's Method.

Which is what the excel solver does

In javascript:
http://aeroweenie.com/calc.html

---

---

Kat Hunter reports on the San Dimas Stage Race from inside the GC winning team
Aeroweenie.com -Compendium of Aero Data and Knowledge
Freelance sports & outdoors writer Kathryn Hunter

Nice work on that calculator. Who knew that the math of cycling could be so awkward! This equation gets even worse if you take into account wind speed. It becomes an implicit cubic equation with no analytic solution. I ran straight for Wolfram Mathematica!

---

CodyBeals.com | Instagram | TikTok
ASICS | Ventum | Martin's | HED | VARLO | Shimano | 4iiii | Keystone Communications

Maybe I'm not following what you need, but since your bike computer probably rounds to the nearest MPH, and you aren't going to get your wheel speed above 50 mph, just make a column with 1, 2, 3, . . . . . .50 and then put the power equation in the next column copied down 50 rows (50 is probably more than you need). So for every MPH you will have a corresponding power number and can set virtual power zones by MPH ranges and never need TrainerRoad.

Your MPH may not match up to the exact power number you need for your training zone, but since you won't be able to see any MPH numbers on your bike computer down past a whole number, you have to round to the nearest MPH anyway.

Did this few years back using Maple. This is what it spat out:

=1.609*(1/6/b*((108*p+12*3^(1/2)*((4*a^3+27*p^2*b)/b)^(1/2))*b^2)^(1/3)-2*a/((108*p+12*3^(1/2)*((4*a^3+27*p^2*b)/b)^(1/2))*b^2)^(1/3))

where

a=5.24482
b=0.01968
p=power

---

---

len = len + 20; /* add some buffer */

You may as well use one of the exact solutions above, as you only have to type it in once, but for interest I thought I'd try just generating a set of power and speed values using the original equation in Excel, creating a graph with power on the x-axis and speed on the y-axis, then fitting a curve to that.

S = -1.81976995047574E-10*P^4 + 3.43781219093101E-07*P^3 - 0.000254429367943983*P^2 + 0.110321595603561*P + 3.42384924501135


has a max error of 2.1W in the range 100W-400W.

It all depends on what you are trying to do. Are you trying to intellectually solve a math problem? If so I can't help you.


Or are you trying to have something where you type in a power number and it gives you a speed that is as close as needed?

If the later then here is a solution using a lookup chart in excel. In the first tab type the power number you want and it looksup the resulting speed to within .01 MPH.

https://drive.google.com/...ZmM/view?usp=sharing

Thanks for all the replies. Steve Irwin, that reply helped the most since all I had to do was just plug it in to the cell. I know I could have just kept the original formula from KK and reversed the process, but I wanted to be able to plug numbers in either direction. Not having a power meter I wanted to be able to play with the numbers and see what "power" I was putting out and plan trainer workouts around power just cause. Thanks again to all those who contributed.

Todd

I just do a binary search for the answer. Which is, I learned later, a ghetto Newton's Method.

One of the first things I learned to do on the computer. I hate math. Ghetto is good!