Custom addition formula

[Tim Whiteaway]

Using Office2000 Sp3

I use Excel a lot for historical things including eg; areas of land
expressed in ACRES ROODS PERCHES which is not added in tens (similar to
£/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a
formula which when applied to entries across 3 columns gives a decimal
answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in
D1, Roods E1, perches F1. Once this is decimalised I can work out ratios
etc.

However, I occasionally get tabbed text files or excel files from people who
have entered lots of raw data as eg ARP where it would be useful to just add
the Acres, Roods Perches as they are, similar to autosum except across 3
columns AND get it to add in something other than base 10. How can i make a
formula or autosum add numbers in a column in base 40 ie 0-39, if 39 is
exceeded 1 is added to the column of the left and the original reset to zero
and when 3 is exceed in that column the same happens to the column on its
left.

A R P
2 1 13
3 2 34
6 3 10

Formula addition =12.79375 acres


But as an addition it would normally add as 11 6 57 when the correct answer
is really 12 3 17.

TIA

Tim Whiteaway

 

[Eric]

Tim,

Hopefully this works for you. I used your Raw data
beginning in cell A1 as follows:

A B C
1 2 1 13
2 3 2 34
3 6 3 10

Then I entered the following in Row4:

A4: =SUM(A1:A3)+ROUNDDOWN(SUM(B1:B3)/4,0)
B4: =MOD(SUM(B1:B3)+ROUNDDOWN(SUM(C1:C3)/40,0),4)
C4: =MOD(SUM(C1:C3),40)

This returned 12 3 17 in those respective cells.

Hope that helps!

Eric

 

[Tim Whiteaway]

Many thanks Eric, exactly what I was looking to achieve!

Tim