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
anthonytrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
FarWestConnect With a Mentor Commented:
Is this what you want?
currency breaker
attached is the excel file with formulas
Book1exch.xlsx
0
 
gowflowCommented:
can you post your workbook. Easier to work with ?
gowflow
0
 
anthonytrAuthor Commented:
Fryezz: Yes.  What is the "1.20" referring to?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FarWestCommented:
this is the remaining amount after each break, and that is the amount you need to use for the next break
0
 
anthonytrAuthor 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
 
FarWestCommented:
aha,
is it possible for you to use macros, and vba
or you need none-macro solution?
0
 
gowflowCommented:
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
 
anthonytrAuthor Commented:
It's needs to be non macro I'm afraid.  I'm using office for Mac.
0
 
gowflowCommented:
Our answers crossed. Pls check my solution just posted.
gowflow
0
 
FarWestCommented:
check this, it will allows you to do that without macros
Book1exch2.xlsx
0
 
anthonytrAuthor 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
 
anthonytrAuthor 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
 
gowflowCommented:
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
 
anthonytrAuthor 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
 
barry houdiniConnect With a Mentor 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
 
FarWestCommented:
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
 
gowflowCommented:
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
 
Martin LissOlder 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
 
barry houdiniCommented:
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.