What are the formulas for adding pounds, shillings & pence?

Hi CBroach,

First add up pounds, shillings and pence to find the "Raw Sum" of each (Row 2).

30 shillings is 1 pound ten shillings. Ten shillings is the remainder after you divide 30 shillings by 20. The MOD function will do that for you.

Formula in C3 =MOD(C2,20)

Carry the "extra" pound(s) into the pounds column. Formula in B4 =INT(C2÷20)

18 pence is 1 shilling and sixpence. Sixpence is the remainder after you divide 18 pence by 12. The MOD function will do that for you.

Formula in D3 =MOD(D2,12)

Carry the "extra" shilling(s) into the shillings column. Formula in C4 =INT(D2÷12)

Formula in B8 =B2+B4

Formula in C8 =C3+C4

Formula in D8 =D3

Regards,

Ian.

I don't think there are any built-in "formulas" that do that. An equivalent question would be "what are the formulas for adding dollars, quarters, dimes and nickels". You add them based on their percentage of a dollar.

If I have it correct,

Shillings/20 converts shillings to pounds

Pence/12 converts pence to shillings

Pence/240 converts pence to pounds

Once they are all in a common currency (such as pounds), you can add them.

=Pence/240 + Shillings/20 + Pounds

I just tried using a similar process. Converted the columns of Pounds, Shillings and Pence from Table 1 (top table below) to ALL PENCE. Then summed the PENCE (in Table 1-1 below).

Then back to adding formulae to produce the total row in Table 1. The screenshot shows the various formulae.

Hi Barry,

Barry asked: What's the speed of light, measured in furlongs per fortnight?

186,000 entered in A2

1 entered in B2

Formula in C2 =A2×8 (8 furlongs per mile)

Formula in D2 =B2÷(60×60×24×14)

Formula in E2 =C2÷D2

Interesting that furlong comes from furrow long

"The furlong (meaning furrow length) was the distance a team of oxen could plough without resting."

https://en.wikipedia.org/wiki/Furlong

Thanks for the challenge!

Regards,

Ian.

Hi CBroach,

"…a total that must be separated into 2 constituent parts—a dividend (i.e., the whole number which is added to the next superior coin) & a remainder (i.e., that amount which is left as the total of the inferior coin)."

Minor vocabulary correction: That total is the dividend, the number that is to be divided by the divisor. The whole number result of that division is the quotient, and what's left over is the remainder.

Here's a version using essentially the same formulas as yours (with some syntax corrections), and doing only the row totals for each column on the main "Data" table, and the rest in a single row of a "Rationalization" table:

Use of a Header row and a Footer row on data permits summing all rows between with only a column reference in the SUM fuction.

B13: SUM(B)

C13: SUM(C)

D13: SUM(D)

Working from right to left in row 2 of Rationalization:

C2: MOD(Data::D13,12)

Returns the Remainder, when the penny total is divided by 12.

B2: MOD(SUM(Data::C13,INT(Data::D13÷12)),20)

Sums the raw total from the shillings column (C) and the quotient of the pennies total divided by 12 to get a 'grand total' of shillings, then returns the remainder when that grand total is divided by 20.

A2: SUM(Data::B13,INT(SUM(Data::C13,INT(Data::D13÷12))÷20))

Recalculates the grand total of shillings (italics part), this time dividing it by 20 and extracting the quotient, then sums this quotient and the raw total of the Pounds column (B13) to get the grand total of pounds.

Numbers also supports the QUOTIENT function, which returns the whole number result of dividing the dividend by the divisor. The three formulas above, and there equivalents using QUOTIENT, are listed below.

C2: C2: MOD(Data::D13,12)

(no change)

B2: MOD(SUM(Data::C13,INT(Data::D13÷12)),20)

B2: MOD(SUM(Data::C13,QUOTIENT(Data::D13,12)),20)

A2: SUM(Data::B13,INT(SUM(Data::C13,INT(Data::D13÷12))÷20))

A2: SUM(Data::B13,QUOTIENT(SUM(Data::C13,QUOTIENT(Data::D13,12)),20))

Regards,

Barry

Hi Barry,

From the perspective of someone who grew up with a decimal currency, LSD conversion is complicated.

That made me smile! I grew up in the era of British Imperial units, and it was "natural" (i.e. this is how we have always done it) to "carry" non-decimal units to the next left column. Yards, feet and inches; tons, hundredweights and quarters; gallons, quarts and pints. All with paper and pencil 😎.

Regards,

Ian.

Hi Ian,

I did the inches, feet, yards, rods, chains, furlongs, miles and acres; the pounds and ounces and long and short tons; gallons, quart and pints (Imperial and US) and even the bushel and a peck and a hug around the neck bits, but Canada had turned to dollars and 'sense' long before I started counting change, so I didn't get much early experience with Sterling.

Regards,

Barry

Calculation question for you,from a first year physics class: What's the speed of light, measured in furlongs per fortnight?

(I've forgotten.)

Hi CBroach,

Adding a column of shillings &/or pence results in a total that must be separated into 2 constituent parts—a dividend (i.e., the whole number which is added to the next superior coin) & a remainder (i.e., that amount which is left as the total of the inferior coin).

Your explanation is better than my explanation because you are correctly starting in the pence column and working leftwards "carrying" every extra 12 pence (shillings) into the shillings column, and then "carrying" every extra 20 shillings (pounds) into the pounds column.

Thank you for the green tick, your feedback and this reminder that not everything is decimal! For example, a dozen eggs 😎.

Regards,

Ian.

Similar approach to Ian's, but erred in one formula

Deleted by poster.

Barry