Solved

# Break Currency down into individual notes and coins

Posted on 2015-01-05
124 Views
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
Question by:anthonytr
• 6
• 5
• 5
• +2

LVL 29

Expert Comment

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

LVL 12

Accepted Solution

FarWest earned 250 total points
ID: 40532721
Is this what you want?

attached is the excel file with formulas
Book1exch.xlsx
0

Author Comment

ID: 40532726
Fryezz: Yes.  What is the "1.20" referring to?
0

LVL 12

Expert Comment

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

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

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

LVL 29

Expert Comment

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

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

LVL 29

Expert Comment

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

LVL 12

Expert Comment

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

Author Comment

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

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

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

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

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

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

ID: 40551123

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

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

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

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 …
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…