Solved

Break Currency down into individual notes and coins

Posted on 2015-01-05
22
125 Views
Last Modified: 2016-02-11
Hi,

I am trying to break down a given currency amount into the relevant Notes and Coins to make up the amount.  The Currency is British Pound (£20, £10, £5, £2, £1, £0.50, £0.20, £0.10, £0.05, £0.02, £0.01).

My sheet is setup with the amount running down the left (A2 - A20) and the different Notes/Coins are across the top B1- L1.

I have tried the follow formula, but it's not quite working out:
In B2 i have
=INT(A2/B$1)

=INT(($A2-SUMPRODUCT(($B$1:B$1)*($B2:B2)))/C$1)
This is copied into the remaining cells to the left.

I'm not sure if I'm calculating the Pounds and the Pence correctly, if I enter £41.40 it calculates:
£20 = 1
£10 = 0
£5 = 0
£2 = 0
£1 = 1
0.50 = 0
0.20 = 1
0.10 = 1
0.05 = 1
0.02 = 2
0.01 = 1

Which isn't quite right.  

Can anyone see where I going wrong in my formulas?

Thanks,
0
Comment
Question by:anthonytr
  • 6
  • 5
  • 5
  • +2
22 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40532719
can you post your workbook. Easier to work with ?
gowflow
0
 
LVL 12

Accepted Solution

by:
FarWest earned 250 total points
ID: 40532721
Is this what you want?
currency breaker
attached is the excel file with formulas
Book1exch.xlsx
0
 

Author Comment

by:anthonytr
ID: 40532726
Fryezz: Yes.  What is the "1.20" referring to?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:FarWest
ID: 40532732
this is the remaining amount after each break, and that is the amount you need to use for the next break
0
 

Author Comment

by:anthonytr
ID: 40532751
I see that now!  Sorry - my ignorance.

If I needed to add a list of amounts down the left, how would this be done?  I would then simply add up how many different notes and coins I need and this can be sent to our treasurer who will request it from our bank.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40532758
aha,
is it possible for you to use macros, and vba
or you need none-macro solution?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40532766
Is this what you want ?
pls chk the file the formulas are all the same from Col C to K and are
=INT(($A2-SUMPRODUCT($B1:B1,$B2:B2))/C1)

For Col L you need to remove the Int to get the correct change
=($A2-SUMPRODUCT($B1:K1,$B2:K2))/L1

Try the file I added last column that will total the break received to confirm that the formula is correct.
gowflow
MoneyBreaker.xlsx
0
 

Author Comment

by:anthonytr
ID: 40532768
It's needs to be non macro I'm afraid.  I'm using office for Mac.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40532770
Our answers crossed. Pls check my solution just posted.
gowflow
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40532774
check this, it will allows you to do that without macros
Book1exch2.xlsx
0
 

Author Comment

by:anthonytr
ID: 40533278
fryezz:  Thanks for the document.

I'm not sure if i'm doing something wrong, but the pence columns seem to be calculating things wrong.
Book1exch2.xlsx
0
 

Author Comment

by:anthonytr
ID: 40533288
glowflow: the document you attached has the same problem as my original formulas.  It works out the Notes (£20, £10 & £5) ok, but the pounds and pence are not correct.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40533417
Sorry I don't see where it is wrong ! lets take this example:


Amt      £20.00      £10.00      £5.00      £2.00      £1.00      £0.50      £0.20      £0.10      £0.05      £0.02      £0.01      Test

£48.23      2      0      1      1      1      0      1      0      0      1      1      £48.23

we have: 48.23 = 2x20 + 1x5 + 1x2 + 1x1 + 1x0.20 + 1x0.02 + 1x0.01
don't see what is wrong.

pls provide an example where it is wrong.
gowflow
0
 

Author Comment

by:anthonytr
ID: 40533481
Simply enter £41.40

The result you get is:
£20 = 2
£1 = 1
20p = 1
10p = 1
5p =1
2p =2
1p =1

Why not 20p = 2 ?
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 40533518
This problem is caused by "rounding errors" which occur in excel, e.g. with 41.40 in A2 then in H2 the result of this part of the formula

=($A2-SUMPRODUCT($B$1:G$1,$B2:G2))/H$1

....is 1.999999999 instead of the expected 2 so when you apply INT function you get 1 instead of 2 and the results are wrong. The simplest solution is to introduce an additional ROUND function to avoid that, e.g. use this formula in C2 copied across and down:

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

That's essentially your original formula with an added ROUND function - it can go in column L too

see here for more on Excel rounding errors

regards, barry
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40535803
sorry, I've been a way, and sorry it is my mistake
just make sure to copy formulas(or cells) that is "S" column in my sheet to all of the cells forward until "AB"
sorry again
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40551123
Your last question was addressed to my proposed solution. You were asking:


Simply enter £41.40

 The result you get is:
£20 = 2
£1 = 1
 20p = 1
 10p = 1
 5p =1
 2p =2
 1p =1

 Why not 20p = 2 ?

Which rightfully, Barry replied to. Did you check the solution proposed ?
gowflow
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40601357
I've requested that this question be closed as follows:

Accepted answer: 500 points for FarWest's comment #a40532721

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40601358
I think my suggestion most closely answers the question. anthonytr says

Can anyone see where I going wrong in my formulas?

I identified what was wrong (rounding errors) and proposed a solution which doesn't change the approach and doesn't require a different layout, just adds another function to the formula

regards, barry
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question