Non-decimal arithmetic: old £ s d and a negative balance

[jd_jd]

I'm entering details of English personal accounts from 17th & 18th centuries. I convert amounts into farthings (£1=960; 1s=48; 1d=4), and can add them up in farthings: I do this for credit and debit totals, and subtract debit from credit to get a balance which I then reconvert back to
£sd.

The procedure for reconversion (got from a friendly forum for Fmpro) is

£=trunc farthings/960; and I store mod (farthings, 960) in a cell (let's say A57)
s=trunc A57/48; and the mod (A57, 48) goes into A58
d=trunc A58/4
f = mod( A58, 4)

That all works fine, except when debit is greater than credit: the person owes money.
the trunc/mod procedure gives a negative figure for the ££, but the mod(farthings, 960) records as a positive. And the arithmetic for ss and dd appears to go haywire.

So the question is: how can I get a negative balance to reconvert accurately?

All help gratefully received.

[martindwilson]

cant really guess but wrapping in ABS() will give + for any calculation

[martindwilson]

just a joke but do you know what does sun+moon+stars=?

[zbor]

without testing formula on your example workbook, maybe this could work?

=IF(D3<0, "-", "")&Your_formula(ABS(D3))

[Roger Govier]

Hi

The following will work, by taking the abs of the difference before any trunc or mod calculations, then placing a + or - in the column in from of the figures.

take a look at the attached file with one sheet showing all the columns with the formulae, and the other with those columns hidden.

[martindwilson]

sun+moon+stars= 3 farthings....3 far-things

[protonLeah]

...mod(farthings, 960) * SIGN(farthings)...

[jd_jd]

I posted the query at 5.30pm and by 10.30 you generous people had given me 6 answers,
two of which work brilliantly. Many thanks. jd