Solved

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

Posted on 2011-09-21
16
3,745 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

756 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