Solved

Break Currency down into individual notes and coins

Posted on 2015-01-05
22
140 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +2
22 Comments
 
LVL 30

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 30

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 30

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 30

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 30

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

751 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