Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Break Currency down into individual notes and coins

Posted on 2015-01-05
22
Medium Priority
?
164 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 31

Expert Comment

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

Accepted Solution

by:
FarWest earned 1000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 31

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 31

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 31

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 1000 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 31

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 49

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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