pounds and pennies

add the sum of pounds to the sum of pennies divided by 100

Sum of Pounds = 512
Sum of Pennies = 1232

Total sum = (Sum of Pounds) + (Sum of pennies) / 100 = 512 + 1232/100 = 524.32

I cannot imagine why you would want separate columns for pounds and pence on a financial spreadsheet.

Whatever ...

Pounds in column A:

Formula:

Please Login or Register  to view this content.

Pence in column B:

Formula:

Please Login or Register  to view this content.

I suspect that you might get rounding errors; not sure.

Or, you could just add them together row by row:

Formula:

Please Login or Register  to view this content.

and format as currency. Then just add the column.

Or, if you just wanted the total as currency without the intermediate steps:

Formula:

Please Login or Register  to view this content.

Regards, TMS

You can't "carry over" anything from one cell to another. Either the cell has a formula or it has a value; it cannot have both. So if you put 1 in cell A2 and 105 in cell B2, you cannot adjust either column to reflect the fact that the pence column is over 99.

You can adjust the totals because that will be just formulae, as described previously.

Regards, TMS

OK, so I really can't understand why you wouldn't have a single column and key in 1.50 or 1.99 or whatever. Then your sub-totals and totals are very simple.

Regards, TMS

Well, I've given you formulae for the subtotals and totals. You just need to adapt them.

Like I say, on a row you can type or you can have a formula; you can't have both. That said, you could use VBA to monitor the pennies cells and, if they exceed 99p convert it into pounds and pence ... add the pounds to the pounds column and replace the pennies.

Seems a bit extreme but you can do it if you're prepared to go for a code solution.


Regards, TMS

	A	B	C	D
7	Daily Takings
8	Monday		88	2
9	Tuesday		85	52
10	Wednesday		43	99
11	Thursday		98	38
12	Friday		13	0
13	Saturday		33	44
14	Sunday		70	49
15	Total Takings		432	84

C15: =SUM(C8:C14) + INT(SUM(D8:D14)/100)

D15: =MOD(SUM(D8:D14), 100)

That's a whacky way to record financial data.