Formula help, Eric?

[Tim Whiteaway]

I posted a formula query last November which Eric Williams replied to with a
formula that seemed to work. However, when I used it on a sheet with more
entries than my example the answer is clearly wrong.

The formula is
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)

If Eric or anyone else could help sort this out I'd be grateful.

I have posted copies of my original query and Eric's reply together with an
explanation of what I need and Why and How I achieve this now (calculator)
together with a bigger example.

Please look at http://www.ga98tew.eurobell.co.uk/docs/eric.htm

Thanks, All too clever for me

 

[Norman Harker]

Hi Tim!

The following appears to test OK:

A4:
=SUM(A1:A3)+INT(SUM(B1:B3)/4)+INT(SUM(C1:C3)/160)
B4:
=MOD(((SUM(C1:C3)-C4)-(INT(SUM(C1:C3)/160)*160))/40+SUM(B1:B3),4)
C4:
=MOD(SUM(C1:C3),40)

The formulas can probably be simplified but at present they check
against a trio of much more complicated long hand formulas.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Tim Whiteaway]

Norman,

I tried out your formulas;
A4: =SUM(A1:A3)+INT(SUM(B1:B3)/4)+INT(SUM(C1:C3)/160)
B4: =MOD(((SUM(C1:C3)-C4)-(INT(SUM(C1:C3)/160)*160))/40+SUM(B1:B3),4)
C4: =MOD(SUM(C1:C3),40)

And all seemed well, it correctly answered a bigger input block. I then
tried it out with a 64 row block and the answer is not correct, Column A is
1 unit short. I've sat and fiddled with it but can't fathom it all out. I've
posted a new set of examples and my notes to
http://www.ga98tew.eurobell.co.uk/docs/norman.htm

This is another web page with a download link to an Excel.xls file.

Thanks for your time and help

Tim Whiteaway

 

[Norman Harker]

Hi Tim!

Try:
A70:
=SUM(A7:A69)+INT(SUM(C7:C69)/160)+INT((SUM(B7:B69)+(INT(MOD(SUM(C7:C69),160)/40)))/4)

I'm sure it can be done more efficiently, but it seems to work OK
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Tim Whiteaway]

many thanks.

Tim W

 

[Norman Harker]

Hi Tim!

Pleased to help. I was a bit confused as to why the solution worked
some times and not others. The errors occurred when the number of
perches added to the modulus of roods after deducting acres was over 1
acre. That acre got missed off.

Interesting problem! I'll be posting it to the real estate professions
here because a lot of our old title documents state the area in acres,
roods and perches.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.