speed range table equation

oneofmanynameless · 04-20-2021, 01:09 AM

can anyone tell me what the actual equation is for the speed/range table? I'm trying to create a spreadsheet that will auto-calculate the modifier for a given value. I know it involves logarithms but don't really know how to use them that well. I'm working in google docs.

awesomenessofme1 · 04-20-2021, 01:23 AM

You're not going to get a broadly usable equation because it rounds differently at different points.

Ulzgoroth · 04-20-2021, 02:35 AM

Fundamentally, the SSR modifier is equal to six times the log of the measure divided by the zero point.

For distance, that's 6 * log(d / 2yd)

Use base 10 logs.

The positions in the tables are shifted to put the breakpoints at 'nice' numbers though

Quote:

Originally Posted by awesomenessofme1

You're not going to get a broadly usable equation because it rounds differently at different points.

If you're committed to using the table as written rather than having the formula replace it, well...it could probably be done but it would involve some weird functions to force it to emulate the way the table is rounded.

Sam Baughn · 04-20-2021, 06:06 AM

It isn't elegant, but here a google docs sheet which should do the job.
https://docs.google.com/spreadsheets...it?usp=sharing

Varyon · 04-20-2021, 06:29 AM

If you want to keep the current breakpoints, just treating it as linear between them works out just fine - in no small part because the current breakpoints largely are linear, it's just that the slopes keep increasing. What I mean by that is that from -2 to 0 (1, 1.5, 2) and +1 to +3 (3, 5, 7), it's linear (+0.5 per +1 and +2 per +1, respectively), and as there are only 6 unique values (everything else just repeats the same sequence with the decimal point shifted), this means there are only a few cases where you can't draw a straight line through 3 consecutive points (basically 0 to +1 and +3 to +4 are the odd ones out). So, SM +3.7 corresponds to 9.1 yards (7/10ths of the way between 7 and 10).

I'm not certain of an elegant way to have a spreadsheet do that for you, however.

coronatiger · 04-20-2021, 07:12 AM

You can check out my Excel Character Sheet (link in signature) for a formula. In the current version (20.3), it's located on the Current Status sheet in cell BR18 (under header "Penalty"). The formula isn't pretty, and could probably be simplified, at least a little, but it does the trick. It includes "to hit" penalty for the size of the target at the end of the formula, but you can easily remove that part, if you don't want it.

ericthered · 04-20-2021, 07:49 AM

So if you don't mind the slightly off rounding, there is always the equation:

Code:

=Round(Log(D7,10)*6-2,0)

replace D7 with what you need.

the rounding really isn't that bad, its just not exact. for most purposes, this will give you what you need.

RyanW · 04-20-2021, 09:52 AM

If you needed a spreadsheet formula, you could duplicate the SSR table to infinity in both directions with a simple lookup.

=vlookup('size'/int(log('size',10)),'table',2)+6*int(log('size',10 ))

Where 'size' points to the size in yards and 'table' points to a table of SM from -2 (1 yard) to 3 (7 yards). That treats SM+1 as staring just beyond 2 yards, rather than rounding to the closest SM, which GURPS is sometimes inconsistent about (21 yards is -7 for range, but 2.1 yards is a tallish SM+0 human). It also doesn't account for the small differences introduced by the canon chart switching to inches at the small end.

Edit to add: if you want to get really fancy, you could add a drop down selector to pick inches (an additional -11 to SM), feet (-3), or miles (+17 or +18 depending on how you want to round).

Ulzgoroth · 04-20-2021, 10:00 AM

Quote:

Originally Posted by RyanW

That treats SM+1 as staring just beyond 2 yards, rather than rounding to the closest SM, which GURPS is sometimes inconsistent about (21 yards is -7 for range, but 2.1 yards is a tallish SM+0 human).

This is the only place it's inconsistent that way, and seems to be a consequence of the principle that 'a human character's build only has mechanical effects if you want it to'.

whswhs · 04-20-2021, 10:55 AM

I don't think there's a neat way to do this with a spreadsheet, but if you want a fractional modifier, for a number that falls between L and U, you can take

(log X - log L)/(log U - log L)

or

log (X/L)/log (U/L)

and add it to the modifier for L.

So, for example, if X is 2.5, that falls between 2 and 3, so

log (2.5/2)/log (3/2) = log 1.25/log 1.5 = 0.10/0.18 = 0.55

and adding that to 0 (the modifier for 2) is 0.55.

04-20-2021, 01:09 AM	#1
oneofmanynameless Join Date: May 2012 Location: New Hampshire, USA	speed range table equation can anyone tell me what the actual equation is for the speed/range table? I'm trying to create a spreadsheet that will auto-calculate the modifier for a given value. I know it involves logarithms but don't really know how to use them that well. I'm working in google docs.

04-20-2021, 01:23 AM	#2
awesomenessofme1 Join Date: Mar 2016	Re: speed range table equation You're not going to get a broadly usable equation because it rounds differently at different points.

04-20-2021, 06:06 AM	#4
Sam Baughn Join Date: Aug 2004 Location: United Kingdom of Great Britain and some other bits.	Re: speed range table equation It isn't elegant, but here a google docs sheet which should do the job. https://docs.google.com/spreadsheets...it?usp=sharing __________________ My blog.

04-20-2021, 06:29 AM	#5
Varyon Join Date: Jun 2013	Re: speed range table equation If you want to keep the current breakpoints, just treating it as linear between them works out just fine - in no small part because the current breakpoints largely are linear, it's just that the slopes keep increasing. What I mean by that is that from -2 to 0 (1, 1.5, 2) and +1 to +3 (3, 5, 7), it's linear (+0.5 per +1 and +2 per +1, respectively), and as there are only 6 unique values (everything else just repeats the same sequence with the decimal point shifted), this means there are only a few cases where you can't draw a straight line through 3 consecutive points (basically 0 to +1 and +3 to +4 are the odd ones out). So, SM +3.7 corresponds to 9.1 yards (7/10ths of the way between 7 and 10). I'm not certain of an elegant way to have a spreadsheet do that for you, however. __________________ GURPS Overhaul

04-20-2021, 07:12 AM	#6
coronatiger Join Date: Apr 2018 Location: Trondheim, Norway	Re: speed range table equation You can check out my Excel Character Sheet (link in signature) for a formula. In the current version (20.3), it's located on the Current Status sheet in cell BR18 (under header "Penalty"). The formula isn't pretty, and could probably be simplified, at least a little, but it does the trick. It includes "to hit" penalty for the size of the target at the end of the formula, but you can easily remove that part, if you don't want it. __________________ You don't need to spend 100 CP on Status 5 [25] and Multimillionaire [75] to feel like a princess, when Delusion [-10] will do. Character sheet: Google Drive link (See this thread for details.) Campaign logs: Chaotic Pioneering / Confessions of a Forked Tongue

04-20-2021, 07:49 AM	#7
ericthered Hero of Democracy Join Date: Mar 2012 Location: far from the ocean	Re: speed range table equation So if you don't mind the slightly off rounding, there is always the equation: Code: =Round(Log(D7,10)*6-2,0) replace D7 with what you need. the rounding really isn't that bad, its just not exact. for most purposes, this will give you what you need. __________________ Be helpful, not pedantic Worlds Beyond Earth -- my blog Check out the PbP forum! If you don't see a game you'd like, ask me about making one!

04-20-2021, 09:52 AM	#8
RyanW ☣ Join Date: Sep 2004 Location: Southeast NC	Re: speed range table equation If you needed a spreadsheet formula, you could duplicate the SSR table to infinity in both directions with a simple lookup. =vlookup('size'/int(log('size',10)),'table',2)+6int(log('size',10 )) Where 'size' points to the size in yards and 'table' points to a table of SM from -2 (1 yard) to 3 (7 yards). That treats SM+1 as staring just beyond 2 yards, rather than rounding to the closest SM, which GURPS is sometimes inconsistent about (21 yards is -7 for range, but 2.1 yards is a tallish SM+0 human). It also doesn't account for the small differences introduced by the canon chart switching to inches at the small end. Edit to add: if you want to get really fancy, you could add a drop down selector to pick inches (an additional -11 to SM), feet (-3), or miles (+17 or +18 depending on how you want to round). __________________ RyanW - Actually one normal sized guy in three tiny trenchcoats. Last edited by RyanW; 04-20-2021 at 10:48 AM.*

04-20-2021, 10:55 AM	#10
whswhs Join Date: Jun 2005 Location: Lawrence, KS	Re: speed range table equation I don't think there's a neat way to do this with a spreadsheet, but if you want a fractional modifier, for a number that falls between L and U, you can take (log X - log L)/(log U - log L) or log (X/L)/log (U/L) and add it to the modifier for L. So, for example, if X is 2.5, that falls between 2 and 3, so log (2.5/2)/log (3/2) = log 1.25/log 1.5 = 0.10/0.18 = 0.55 and adding that to 0 (the modifier for 2) is 0.55. __________________ Bill Stoddard I don't think we're in Oz any more.