Thread: formulars
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Richard Buttrey
 
Posts: n/a
Default formulars

On Mon, 20 Mar 2006 15:15:08 -0800, jnf
wrote:

i want to calculate what quantities of each british curency notes and coins
there are in a set amount ie how many £20,£10,£5,£1,50p,20p,10p,5p,2p,1p. i
have used various formulars but when i round it of it gathers the odd amounts
to the end and gives a wrong answer
can anyone help
jnf


You appear to have missed out the £2 coin.

One solution assuming you want to minimise the total number of
notes/coins is:

Enter the Coin Values in B1:L1 in £s.
e.g. 20, 10, 5, 2, 1, 0.5, 0.2, 0.1, 0.05, 0.02, 0.01

Enter the amount you want to split in A2.
In B2 enter =INT($A$2/$B$1)
In C2 enter =INT(($A2-SUMPRODUCT(($B2:B2)*($B$1:B$1)))/C$1)

and then copy C2 across to L2.

B2:L2 gives the number of coins/notes for the relevant denominations

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________