Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulars
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...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=524609 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with formulars | Excel Discussion (Misc queries) | |||
Worksheet formulars | Excel Worksheet Functions | |||
Sorting using nothing but formulars? | Excel Discussion (Misc queries) | |||
Deleting Cells, but not ones with formulars | Excel Discussion (Misc queries) | |||
Can I put 2 formulars in one cell | Excel Worksheet Functions |