Solved

Formula to tell how many currency note of different denominations are present in any given amount

Posted on 2011-09-21
16
3,108 Views
Last Modified: 2012-05-12
Hi there,

Need to design a formula for any given amount that tells how many currency note of the following denominations do exist in it.

1000, 500, 100, 50, 20, 10, 5, 2, 1 and the rest of the amount in pennies or cent or some.

The formula does need to calculate in sequence and then decide what is different denominations with the remaining leftover amount. For example if the amount is "99345.50"

Then the formula should tell that this amount contain 99 currency or banknote of 1000 and now what is left is "345.50" so formula should tell that 0 banknote for 500 and 3 for 100 and now what is left is "45.50" so 0 for 50 and 2 for 20 and 0 for 10 and 1 for 5 and 0 for 2 and 0 for 1 and 0.5 for penny or cent or some...hope i make it clear?


I am trying it but it is getting a little complex...please see the attached MS Excel file.

Thanks

Formula-to-Split-any-Amount.xlsx
0
Comment
Question by:masmatc
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 36572394
Use the function

MOD(A2;1000)  

to find the remaining rest after applying the 1000 division  and use

FLOOR(A2;3)

to get the count of 1000 notes.

The same can be done with the rest for all other values.
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 36572399
ups... I meant

FLOOR (A2/1000;3)
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 36572404
*argh* my notebook keyboard is driving crazy today...

Last correction:

FLOOR(A2/1000;1)
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36572428
As it's not just a simple formula, here is a modified workbook - I have added a row (which you could hide) that contains the denominations at the top. This solution is based on using SUMPRODUCT, but I'm sure there are other ways. Formula-to-Split-any-Amount-1-.xlsx
0
 

Author Comment

by:masmatc
ID: 36572457
HugoHiasl:

if possible...can you please apply the formula in the attached Excel file

andrewssd3:

I am checking your provided modified workbook and will let you know
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 36572483
This is much to complicated. I implemented my version also in the workbook

 Copy-of-Formula-to-Split-any-Amo.xlsx
0
 

Author Comment

by:masmatc
ID: 36572500
andrewssd3:

thanks so far looks good to me...will test it more

The dollar sign is for absolute reference or some right? and i guess it has to be like this(excuse my excel skills)

And what about the pennies or cent...formula is not showing it?
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36572531
Hi - apologies - I had not taken the INT() formula off the last column, so it was not showing the fractional part.  Yes the dollar signs make the references absolute, so that you can safely drag the formulas down to new rows and they will still work Formula-to-Split-any-Amount-1-.xlsx
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:masmatc
ID: 36572570
HugoHiasl:

Looks good...however have a list of amount in the column A...like first amount in A2 then second
amount is in A3 and other amount to split is in A4 so on and so forth...and the provided solution is simple but spread on two rows...any suggestion for that please!
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36572652
The reason the formulas look more complicated in my solution is that I have put everything on one row, rather than maintaining a running total in another row. You could change the formulas in HugoHiasl's solution to do it all in one row, but I think the result would look pretty much like the one I suggested, but maybe using the FLOOR formula instead.
0
 

Author Comment

by:masmatc
ID: 36572717

andrewssd3:

your solution is working fine and i need to have it in one row...so that is awesome...however a little complex depending on where you are on the Excel skill.

How to change the formulas in HugoHiasl's  solution to do it all in one row (using FLOOR or both MOD and FLOOR)...it seems this way that FLOOR and MOD are little simple to understand and explain to others as compared to Array concept.

Can you please use MOD and FLOOR to give the same result? appreciate your help
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36573154
Not for Points

Hello masmatc,

In my opinion andrewssd3 has suggested the simplest generic solution available if you need the calculations on a single row. A simplified approach (with MOD) would only be possible if each denomination is a factor of the previous denomination....and that isn't the case here because 20 is not a factor of 50 and 2 is not a factor of 5.

SUMPRODUCT is a useful function to learn - useful for many things but used here in it's simplest form, multiplying pairs of numbers from two ranges and summing those products.

regards, barry
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36573157
I'm not sure that it can be done more simply in one row - you are always left with the problem of how to keep the running total going without having an extra 'helper' row.  If you look at the formula, it's not actually so complicated: Col B has a different formula as it's the first, so we just divide the initial amount by the first denomination.  The denominations are in Row2 so they would be easy to change if you wanted to this for another currency system (e.g. in the UK we don't have 1000 or 500 notes).

Columns C through J have actually the same formula.  That's the reason for the dollar signs, so you can enter the formula in col C and drag it across to row J. Each of those formulas takes the initial amount, and subtracts the sum of the previous columns so far (SUMPRODUCT multiplies the number of notes by the denomination, and adds this up for each column).  The formulas look a little different because each one sums an extra column. The INT function just throws away the fractional part. If you did not use SUMPRODUCT here you'd probably have to use an array formula entered with ctrl-shift-enter, which would be less easy to understand.

Then the final column is just the same, but without the INT, so that we keep the fractional part.

I really like simple solutions, but I can't see a better way of doing this.  It's flexible so you can add or change denominations, and you can add new rows just by dragging down.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36573302
Barry - thanks for the backup!  Our posts crossed over so I hadn't seen yours when I was typing my long explanation.

Stuart
0
 

Author Comment

by:masmatc
ID: 36573463
EXCELLENT!! WORK in designing and explaining the solution

Many thanks to all and specially to andrewssd3:
0
 

Author Closing Comment

by:masmatc
ID: 36573477
Timely, detailed and brilliant!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now