Thread: Adding/Subtracting/etc money values that are split into £ and p columns

Attached is part of the sheet I'm working on.

All my £ values are in seperate columns - one for £, the other for p. I need to be able to calculate totals, and % of these totals, and subtract, all whilst keeping the column format. I've tried many ways of doing this with the MOD function, but I can only get a few bits to work (mainly the addition bits). I'm hoping my explanation on the sheet itself is clear enough as to what I'm after.
The % need to be reference to the % cells in Column E though, as these are not consistant numbers, and sometimes do not need to be included at all.

Many thanks in advance for any help

Originally Posted by Bob Phillips

Here is a starter

G38: =INT(SUMPRODUCT(--(G1:G36+H1:H36/100)))
H38: =INT(SUMPRODUCT(--(G1:G36+H1:H36/100)))

G40: =INT((G38+H38/100)*E40)
H40: =((G38+H38/100)*E40-G40)*100

I will leave the rest to you, but I would urge you to change the layout, it is nonsense.

Thanks for that...

Regarding the layout, trust me, if I had the power to amend it to be the full figure in just one cell, I would. Management, as always, know best and insist is stays as it is, hence why I'm trying to make it work in the ****-eyed fashion it is.

I have tried your above formulas, and before I even try to get my head around how they work/what they do (I'm familiar with 'INT', but 'SUMPRODUCT' and the -- bit baffles me...) I've noticed the last one for H40 gives an answer of 100 (with the current figures on the example). I'm more than happy to play around with it and make your 'starters' work for the rest, but are you able to amend/fix/etc that last one please?

Thanks again for your time..

Originally Posted by dancing-shadow

Thanks for that...

Regarding the layout, trust me, if I had the power to amend it to be the full figure in just one cell, I would. Management, as always, know best and insist is stays as it is, hence why I'm trying to make it work in the ****-eyed fashion it is.

I have tried your above formulas, and before I even try to get my head around how they work/what they do (I'm familiar with 'INT', but 'SUMPRODUCT' and the -- bit baffles me...) I've noticed the last one for H40 gives an answer of 100 (with the current figures on the example). I'm more than happy to play around with it and make your 'starters' work for the rest, but are you able to amend/fix/etc that last one please?

I get 94 in there, not a hundred, which is correct as 10% of 99.36 is 9.936 which rounds to 9.94.

For more info on SUMPRODUCT and the dreaded double unary, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html, it might be a bit heavy.

A simpler way to manage it might be to reference t all from another worksheet, calculate ther, and then split up the results.

If you get stuck on it just shout for more.