Solved: Formula help for odd money calculation?

[Simon Lloyd]

Hi all, a friend of mine has designed a sheet for his daughters business, it shows cheques/money taken then the amount of vat that is a portion of the cheque/money taken and he wants a value shown which is the cheque/money less VAT....i know what your thinking....dead easy, yep will so did i until he sent me the format he wants it in. He wants to enter the ?'s in one column and the pence in another for the cheques/money and the same for the VAT they are charging,the less VAT columns will have the same format.

The problem is how to deduct the pence from the pounds? the layout and result is as follows e.g please read this as columns
A,...B,...C,....D,...E,..F
?22 23p ?12 99p ?9 24p

Please see attached!

[Norie]

Simon

Why would your friend want such a format/layout?

Perhaps you should suggest a different approach as this format just seems to complicate matters.

[Simon Lloyd]

Lol i did suggest using a single for the figure i.e ?22.23 and life would be very simple, however his daughter already uses this method in her book keeping and wants to be able to transfer the figures as she sees them, a column of ?' and a column of pence. To me the method is quite inane!, it's adding a great deal of work and complexity to what should be a simple task!

[Bob Phillips]

=INT(J3+K3/100-(M3+N3/100))

=MOD(J3+K3/100-(M3+N3/100),1)*100

[Simon Lloyd]

Way beyond me Bob, thanks, i'd never had got it!, i'm still not happy with his set up but who am i to argue?

[Aussiebear]

Perhaps if the daughter was shown a working alternative.... since she is the decision maker in this issue.