Creating a Transit Time Function

[TMackie]

I am looking at creating a custom function to calculate transit time which goes by =roundup(miles/44,0) but then +10 for every 500 miles i.e.
0-500 is the base function
501-1000 is =roundup(miles/44,0)+10 and so forth to a cap of 5000 miles.

I know the base formula would be like this
Function TransitTime(Miles As Single) As Single

TransitTime = RoundUp(Miles / 44, 0)

End Function

But what is the process of creating an if statement for the mileage bands because I tried this below and it didn't work. Miles is what the cell will base the formula off of.

Function TransitTime(Miles As Single) As Single
If Miles < 500 Then
TransitTime = RoundUp(Miles / 44, 0)
Else
If Miles > 500 And Miles < 1000 Then
TransitTime = Round(RoundUp(Miles / 44, 0) + 10, 2)
Else
End Function

 

[dreid1011]

This can be worked into a function, but for the moment, do the results look like what you're after?

Book1
	A	B
1	Miles	Transit Time
2	250	6
3	500	22
4	750	28
5	1000	43
6	1250	49
7	1500	65
8	1750	70
9	2000	86
10	2250	92
11	2500	107
12	2750	113
13	3000	129
14	3250	134
15	3500	150
16	3750	156
17	4000	171
18	4250	177
19	4500	193
20	4750	198
21	5000	214
22	5250	220
23	5500	225
Sheet3

Cell Formulas
Range	Formula
B2:B23	B2	=ROUNDUP(A2/44,0)+(MIN(INT(A2/500),10)*10)

 

[TMackie]

This can be worked into a function, but for the moment, do the results look like what you're after?

Book1
	A	B
1	Miles	Transit Time
2	250	6
3	500	22
4	750	28
5	1000	43
6	1250	49
7	1500	65
8	1750	70
9	2000	86
10	2250	92
11	2500	107
12	2750	113
13	3000	129
14	3250	134
15	3500	150
16	3750	156
17	4000	171
18	4250	177
19	4500	193
20	4750	198
21	5000	214
22	5250	220
23	5500	225
Sheet3

Cell Formulas
Range	Formula
B2:B23	B2	=ROUNDUP(A2/44,0)+(MIN(INT(A2/500),10)*10)

Yes, that is how I want it to look, but what is the best way to build it as a function with VBA? Instead of creating a loop use ROUNDUP(A2/44,0)+(MIN(INT(A2/500),10)*10) instead?

 

[dreid1011]

I'm not quite sure what you're asking. It works just fine as a worksheet formula, what is the need to make it a function? And why the thought of needing to loop?

 

[TMackie]

I'm not quite sure what you're asking. It works just fine as a worksheet formula, what is the need to make it a function? And why the thought of needing to loop?

So using your formula am I wanted to create a formula in VBA that allows me to store the formula you provided within TransitTime.

This formula will work with different people, but the more that I have to build into the function the more likely it gets confusing for others.

 

[Dave Patton]

A UDF will require VBA.
You can use Lambda to build your own function.
Are the results what you expect?
What happens after 5000 miles?

see detail in second example

T202312a.xlsm
	A	B	C
1	Miles	1000	43
2
6f

Cell Formulas
Range	Formula
C1	C1	=TransitTime(B1)

Lambda Functions
Name	Formula
TransitTime	=LAMBDA(Miles,LET(m,Miles,IF(m<5001,ROUNDUP(m/44,0)+IF(m>500,(MIN(INT(m/500),10)*10),0),"")))

With name Manager, I named a new function.
I named the function TransitTime; name the function something appropriate.

T202312a.xlsm
	A	B	C
1	Miles	Transit Time
2	250	6	6
3	500	12	12
4	750	28	28
5	1000	43	43
6	1250	49	49
7	1500	65	65
8	1750	70	70
9	2000	86	86
10	2250	92	92
11	2500	107	107
12	2750	113	113
13	3000	129	129
14	3250	134	134
15	3500	150	150
16	3750	156	156
17	4000	171	171
18	4250	177	177
19	4500	193	193
20	4750	198	198
21	5000	214	214
22	5250
23	5500
6f

Cell Formulas
Range	Formula
B2	B2	=IF(A2<5000,ROUNDUP(A2/44,0)+(MIN(INT(A2/500),10)*10),"")
C2:C23	C2	=TransitTime(A2)
B3:B19	B3	=IF(A3<5000,ROUNDUP(A3/44,0)+IF(A3>500,(MIN(INT(A3/500),10)*10),0),"")
B20:B23	B20	=IF(A20<5001,ROUNDUP(A20/44,0)+IF(A20>500,(MIN(INT(A20/500),10)*10),0),"")

Lambda Functions
Name	Formula
TransitTime	=LAMBDA(Miles,LET(m,Miles,IF(m<5001,ROUNDUP(m/44,0)+IF(m>500,(MIN(INT(m/500),10)*10),0),"")))

 

[dreid1011]

For the UDF you can use this:

Function TransitTime(ByVal Miles As Long)
'=ROUNDUP(A2/44,0)+(MIN(INT(A2/500),10)*10)
TransitTime = Application.WorksheetFunction.RoundUp(Miles / 44, 0) + (Application.WorksheetFunction.Min(Int(Miles / 500), 10) * 10)
End Function