# Break Currency down into individual notes and coins

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,
LVL 1
###### Who is Participating?

x

Commented:
Is this what you want?

attached is the excel file with formulas
Book1exch.xlsx
0

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

Author Commented:
Fryezz: Yes.  What is the "1.20" referring to?
0

Commented:
this is the remaining amount after each break, and that is the amount you need to use for the next break
0

Author Commented:
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

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

Commented:
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 Commented:
It's needs to be non macro I'm afraid.  I'm using office for Mac.
0

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

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

Author Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
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

Commented:

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

Older than dirtCommented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.