Running Pace formula help

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi good afternoon, hope you can help me please, i have a userform with textbox1 is the Time, Textbox2 is the miles ran and textbox3 will be the pace, in CommandButton1 this is the button to work out the calculation for the pace, i have put in the formula below but it does not work when i put in decimal points in on the time or miles for example, so if i did 5 miles in 10 minutes then it works but if i put 3.11 miles in 27:32 i get an error. Please help, do i need to change the value in textbox1 (time)_ to HH:MM? if so how do i do this? please advise
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

EDIT:
i think i need to do this as well but unsure how Time is in hours : minutes : seconds, Distance is in miles, and Pace is in minutes/mile matching the unit Distance is in.
Pace ≠ Speed

Pace is minutes per 1 unit of distance, while Speed is distance per time unit.

Though they are not equal you can convert between them using the distance form of the top formula:


Problem: How fast is a 8:30 pace
Known: Pace = 8.5 minutes for 1 mile
Time = 1 hour or 60 minutes
Needed: Distance = Time / Pace

Calculations: Distance = 60 minutes/8.5 minutes
= 7.06 miles / hour

So, an 8:30 pace = 7.06 miles/hour speed
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi i have done the hh:mm:ss i think now in my other textboxes and the duration i have changed to number format i think this is correct but still struggling with the original code as it doesnt like the decimal points
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

Private Sub TextBox1_Change()
TextBox1.Value = Format(TextBox1, "hh:mm:ss")
End Sub

Private Sub TextBox2_AfterUpdate()
    If Me.TextBox2 <> "" Then
        Me.TextBox2 = Format(Me.TextBox2 / 100, "#,##0.00")
    End If
End Sub

Private Sub TextBox3_Change()
TextBox3.Value = Format(TextBox3, "hh:mm:ss")
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
hi i sort of got it working now (not properly) with the coding below, but the pace is going over the 60 seconds for example i can get a pace of 8.78 which has gone over the 60 seconds so it should be 9.18. how can i do this please.
VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = (TextBox1.Value / TextBox2.Value)
End Sub

Private Sub TextBox1__Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Format(Me.TextBox1.Value, "hh:mm:ss")
End Sub

Private Sub TextBox2__AfterUpdate()
With Me.TextBox2
If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0.00")

End With
End Sub

Private Sub UserForm_Initialize()
With Me.TextBox2
    .Value = 0
    .Value = Format(.Value, "0.00")
End With
End Sub
Private Sub TextBox3__Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox3.Value = Format(Me.TextBox3.Value, "hh:mm:ss")
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi hope you can help me please? I hope it all makes sense? Basically what I want is textbox1 (time( divide by textbox2 (distance) and total of minutes in textbox2 (pace) but at moment the the equation is going over the 60 seconds for example 8.89 secs but as its gone over the 60 it should be 9.29 secs. Hope you can help
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

Hi hope you can help me please? I hope it all makes sense? Basically what I want is textbox1 (time( divide by textbox2 (distance) and total of minutes in textbox2 (pace) but at moment the the equation is going over the 60 seconds for example 8.89 secs but as its gone over the 60 it should be 9.29 secs. Hope you can help
Hi how do i put this in correctly? Do you think this would work?

VBA Code:
Private Sub CommandButton1_Click()
    TextBox3.Value = TextBox1.Value / (TextBox2.Value*24)
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi how do i put this in correctly? Do you think this would work?

VBA Code:
Private Sub CommandButton1_Click()
TextBox3.Value = TextBox1.Value / (TextBox2.Value*24)
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi i have updated my code now to the below after many hours researching on the internet, but i am getting one line turning red please cab you advise?
This is the line where it is all red
Code:
value = CSng((TextBox2.Text * 60) / TextBox3.Text))

This is the whole code:
VBA Code:
Private Sub CommandButton1_Click()
Dim sec As Single
Dim min As Integer
Dim value As Single

value = CSng((TextBox2.Text * 60) / TextBox3.Text))
min = Int(value)
sec = (value Mod min) * 60

TextBox1.Text = Str$(min) & ":" & Str$(sec)
End Sub

Private Sub CommandButton2_Click()
Dim x As Integer
For x = 1 To 3
   Select Case x

     Case Else
         Me.Controls("textbox" & x).value = ""
   End Select
Next x
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub





Private Sub TextBox1_Change()
Dim inMin As Integer
Dim inSec As Integer

inMin = Left$(TextBox1.Text, 1)
inSec = Right$(TextBox1.Text, 1)
End Sub

    
Private Sub TextBox3_Change()
Dim inMin As Integer
Dim inSec As Integer

inMin = Left$(TextBox3.Text, 3)
inSec = Right$(TextBox3.Text, 3)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top