Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: formulars

20 views
Skip to first unread message

Richard Buttrey

unread,
Mar 20, 2006, 7:38:58 PM3/20/06
to
On Mon, 20 Mar 2006 15:15:08 -0800, jnf
<j...@discussions.microsoft.com> 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
__________________________

jnf

unread,
Mar 21, 2006, 5:53:19 AM3/21/06
to
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

"Richard Buttrey" wrote:

> On Mon, 20 Mar 2006 15:15:08 -0800, jnf
> <j...@discussions.microsoft.com> 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.

daddylonglegs

unread,
Mar 21, 2006, 6:27:57 AM3/21/06
to

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: http://www.excelforum.com/member.php?action=getinfo&userid=30486
View this thread: http://www.excelforum.com/showthread.php?threadid=524609

Richard Buttrey

unread,
Mar 24, 2006, 7:05:12 PM3/24/06
to

Sorry, this doesn't get rid of the rounding problem. Copy C2 across to
K2 and then make the last cell L2

=(A2-SUMPRODUCT((B1:K1)*(B2:K2)))/L1

0 new messages