software

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

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

gt;i want to calculate what quantities of each british curency notes and coins
gt;there are in a set amount ie how many ?20,?10,?5,?1,50p,20p,10p,5p,2p,1p. i
gt;have used various formulars but when i round it of it gathers the odd amounts
gt;to the end and gives a wrong answer
gt;can anyone help
gt;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 .
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
__________________________

thank you for reply
i didnt use the same formular as you but got a simular problem
in your formular i put 179.88 into a2 and it misses the 1p off

quot;Richard Buttreyquot; wrote:

gt; On Mon, 20 Mar 2006 15:15:08 -0800, jnf
gt; gt; wrote:
gt;
gt; gt;i want to calculate what quantities of each british curency notes and coins
gt; gt;there are in a set amount ie how many 拢20,拢10,拢5,拢1,50p,20p,10p,5p,2p,1p. i
gt; gt;have used various formulars but when i round it of it gathers the odd amounts
gt; gt;to the end and gives a wrong answer
gt; gt;can anyone help
gt; gt;jnf
gt;
gt; You appear to have missed out the 拢2 coin.
gt;
gt; One solution assuming you want to minimise the total number of
gt; notes/coins is:
gt;
gt; Enter the Coin Values in B1:L1 in 拢s.
gt; e.g. 20, 10, 5, 2, 1, 0.5, 0.2, 0.1, 0.05, 0.02, 0.01
gt;
gt; Enter the amount you want to split in A2.
gt; In B2 enter =INT($A$2/$B$1)
gt; In C2 enter =INT(($A2-SUMPRODUCT(($B2:B2)*($B$1:B$1)))/C$1)
gt;
gt; and then copy C2 across to L2.
gt;
gt; B2:L2 gives the number of coins/notes for the relevant denominations
gt;
gt; HTH
gt;
gt;
gt; __
gt; Richard Buttrey
gt; Grappenhall, Cheshire, UK
gt; __________________________
gt;


Looks like a rounding error, try this in C2

=INT((ROUND($A2-SUMPRODUCT($B2:B2,$B$1:B$1),2))/C$1)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=524609