Solved

Break Currency down into individual notes and coins

Posted on 2015-01-05
22
129 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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