+ Reply to Thread
Results 1 to 4 of 4

formulars

  1. #1
    jnf
    Guest

    formulars

    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

  2. #2
    Richard Buttrey
    Guest

    Re: formulars

    On Mon, 20 Mar 2006 15:15:08 -0800, jnf
    <jnf@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
    __________________________

  3. #3
    jnf
    Guest

    Re: formulars

    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
    > <jnf@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
    > __________________________
    >


  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,699
    Looks like a rounding error, try this in C2

    =INT((ROUND($A2-SUMPRODUCT($B2:B2,$B$1:B$1),2))/C$1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1